Adding SQL Server logins and user accounts for multiple databases in one pass

We have a few applications that include bits that are service applications.  These applications talk to SQL Server databases and they authenticate using SQL Server logins, as opposed to using Windows Authentication.  We recently changed the account that the services were using to a new account with a more robust password.  When the services are deployed, they will have an update mechanism that will add the new login account.  For internal testing, I wanted to give our testers the ability to add the server login and database user for all of their test databases in one shot.  I ended up with a script that looked like this:

   1: use [master]

   2:  

   3: -- create server login if if does not already exist

   4: if not exists (SELECT 1 FROM sys.sql_logins where name = 'myservice') 

   5: create login myservice with password = 'wEEzPHUxce0xhzQFEeF8OHS2KqYYkxTA', Check_expiration = OFF

   6:  

   7: -- Create a cusror that will let us iterate through a list of user 

   8: -- created databases, skipping over the system databases

   9: declare c cursor fast_forward for 

  10:   select name 

  11:   from master.dbo.sysdatabases 

  12:   where name not in ('master', 'tempdb', 'pubs', 'model', 'msdb')

  13:  

  14: declare @name varchar(128)

  15: declare @sql varchar(800)

  16:  

  17: open c

  18:  

  19: fetch next from c into @name

  20:  

  21: while @@fetch_status = 0

  22: begin

  23:   print @name -- display the name of the database

  24:  

  25:   -- Drop the user and schema. this may need to be augmented depedning how you define your roles

  26:   select @sql = 'use ['+@name+'];if exists (select 1 from information_schema.tables where Table_name in (''SomeTableUniqueToMyApp'', ''AnotherTableUniqueToMyApp'')) if exists (select 1 from sys.database_principals where name = ''myservice'') begin IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = ''myservice'') begin DROP SCHEMA myservice end drop user myservice end'

  27:   execute (@sql)

  28:   

  29:   -- Add the user

  30:   select @sql = 'use ['+@name+'];if exists (select 1 from information_schema.tables where Table_name in (''SomeTableUniqueToMyApp'', ''AnotherTableUniqueToMyApp'')) if not exists (select 1 from sys.database_principals where name = ''myservice'') create user myservice for login myservice'

  31:   execute (@sql)

  32:  

  33:   -- Define the user's role  

  34:   select @sql = 'use ['+@name+'];if exists (select 1 from sys.database_principals where name = ''myservice'') execute sp_addrolemember db_owner, myservice'

  35:   execute (@sql)

  36:   

  37:   fetch next from c into @name

  38: end

  39:  

  40: close c

  41:  

  42: deallocate c

This script starts off in the master database and will require admin access rights to the server.  If we break down the script, we can review what each section does and why it’s doing it

Lines 4-5 will create the login if it does not already exist.  The Check_Expiration option is set to “OFF” to verify that password expiration policy will not be applied to this login account.  The BOL for SQL Server 2005 states that this option defaults to “OFF”, but I like to explicitly set that option for a couple of reasons.  One reason is that it stands out when you read the script.  Another reason would be that a future service pack or later version of SQL Server could default that setting to on.

Lines 9-12 create a cursor that provides a list of databases.  We skip over the system tables to save some processing.

Lines 17-22, 37-42 handle the mechanics of opening up the cursor and iterating through each row in the set of database names.

Lines 26-27 create a dynamic SQL statement that performs the following steps and then executes that statement:

1. Change the database context to the current database that was located by the cursor



2. Uses the information_schema.tables view to use this database only if contains two tables that are known to exist in my application database.  This will prevent the user from being added to the wrong database.



3. If this user has a schema, drop the schema.



4. Drop the user from the database.



5. Execute the dynamically created SQL statement.

Lines 30-31 create a dynamic SQL statement that performs the following steps and then executes that statement:

1. Change the database context to the current database that was located by the cursor



2. Uses the information_schema.tables view to use this database only if contains two tables that are known to exist in my application database.  This will prevent the user from being added to the wrong database.



3. Create the user and map that user to the login account created at line 4



4. Execute the dynamically created SQL statement.

Lines 34-35 create a dynamic SQL statement that performs the following steps and then executes that statement:

1. Change the database context to the current database that was located by the cursor



2. Uses the information_schema.tables view to use this database only if contains two tables that are known to exist in my application database.  This will prevent the user from being added to the wrong database.



3. Adds the user to the db_owner role.  Your application may need fewer rights for the user.



4. Execute the dynamically created SQL statement.

We do give our customers the ability to run with just Windows Authentication, but configuring services to run under domain accounts is an additional set of steps that most of our customers prefer not to use.  It’s a much simpler OOBE if the services use SQL Server authentication.