Default Admin account

I have a bit of problem I was hoping someone could point me in the right direction. I have a SQL Server 2005 database which leverages both the Membership and Roles APIs. When I recreate the database for production release, I simply run an sql file using the sqlcmd utility - no problem. What I need is a way to add a default administrator role, account and assign this new administrator to the administrator role.

Can someone advise on how this is typically handled?

There are two permissions in SQL Server the Server Login in the management section of Management Studio and the database permission in the security section of the database in SQL Server 2005. So you create the two user permissions and make the user DBO(database owner) role. The reason is you don't want the person to be Admin in all the databases in that instance of SQL Server. DBO is for database while SA(system admin) is for server. Hope this helps.|||Your post would be useful but I am not using the entities you suggest. I am using the ASP.NET Membership and Roles API (forms authentication) which in short is way to create application specific user accounts and assign them to application specific roles, etc.

Anyone have any suggestions on how to create a default account? Perhaps Transact-SQL calls using the aspnet_Users_CreateUser stored procedure, etc.?|||

That is covered in the two links below in the first link print out the white paper and read it and then you will understand my original post. The roles comes from the database roles. Hope this helps.

http://www.asp.net/sandbox/samp_profiles.aspx?tabindex=0&tabid=1

http://www.jbwebtech.net/Code/ProfileProvider/

|||Greetings Caddre and thanks so much for your help. I'm not quite following you. Please bear with me as I am new to SQL Server. Thanks.

Your last post is in reference to the Profile Provider. I don't see how that addresses my problem as I'm not using the Profile Provider.

I wonder if we are talking about th same users/roles. It seems that the users/roles you speak of are on a database level and the users/roles I am talking about exist in the aspnet_Membership/aspnet_Roles tables respectively. These users and roles that I have defined are specific to my web application only. My ASP.Net web application needs to use the membership and roles API in order to render the appropriate content based on the logged in user and defined role.

During development I would use the ASP.NET Web Configuration tool which utilizes the membership and roles API to add/remove users & roles and assign accordingly. At no point was I required to modify the roles under the MyDB/Security/Roles folder using Server Management Studio.

I'm a bit confused.|||OK, I figured it out. I realize it could look a little nicer...
Thanks for your help.

DECLARE @.UserNamevarchar(20)

SET @.UserName='administrator'

DECLARE @.Passwordvarchar(20)

SET @.Password='ChangeMeAfterFirstLogOn'

DECLARE @.Rolevarchar(20)

Set @.Role='administrator'

DECLARE @.Appnamevarchar(20)

SET @.Appname='MyApplication'

DECLARE @.FullNamevarchar(50)

SET @.FullName='default admin account'

DECLARE @.TimeUTCdatetime

SET @.TimeUTC=GETUTCDATE()

DECLARE @.Datedatetime

SET @.Date=GETDATE()

DECLARE@.membershipIDreturnUNIQUEIDENTIFIER

SET@.membershipIDreturn=NULL

-- Create the default role

EXEC dbo.aspnet_Roles_CreateRole

@.ApplicationName=@.Appname

,@.RoleName= @.Role

-- Create the default account

EXEC dbo.aspnet_Membership_CreateUser

@.ApplicationName=@.Appname

, @.username= @.UserName

, @.password= @.Password

,@.PasswordSalt= N'DVZTktxeMzDtXR7eik7Cdw=='

, @.Email= N'support@.XXXXX.net'

,@.PasswordQuestion= N'FavoritePet?'

,@.PasswordAnswer='%43Dds2ds22@.@.2134%$@.!BffDddwyikbt'

, @.IsApproved= 1

,@.CurrentTimeUtc= @.TimeUTC

, @.CreateDate= @.Date

,@.UniqueEmail= N''

,@.PasswordFormat= 0

, @.UserId= @.membershipIDreturnOUTPUT

IF(@.membershipIDreturnISNOTNULL)

BEGIN

-- Assign the default account the default role

EXEC dbo.aspnet_UsersInRoles_AddUsersToRoles

@.ApplicationName=@.Appname

,@.UserNames= @.UserName

,@.RoleNames= @.Role

,@.CurrentTimeUtc= @.TimeUTC

END

|||I am glad you got it going, I was trying to help you so your admin do not have the right to walk away with your database.