Synchronization of Login and Usernames

Chapter 18 discusses in detail deploying/moving databases from one server to another. The problem you will encounter in this situation is a mismatch between users and logins. This problem is a result of the fact that records in the sys.database_principals catalog view of the copied database point to the records in the sys.server_principals catalog view with matching sid fields. Unfortunately, the same sid value might be used by different logins on two different servers. It is also possible that a login with a specific sid value does not yet exist on a new server. These database users are sometimes referred to as orphaned users. One solution is to create and manage a script that re-creates logins and users on the new server before or after a database is copied.

Another solution is to assemble a script dynamically to create logins on the target server before the database is copied:

 SET NOCOUNT ON SELECT 'CREATE LOGIN [' + name + ']  ' + 'with password = ''Myl.Password'', DEFAULT_DATABASE = tempdb, sid =' ' sid --select * FROM sys.server_principals WHERE principal_id > 256 and type_desc = 'SQL_LOGIN' SELECT 'CREATE LOGIN [' + name + '] FROM WINDOWS; ' FROM sys.server_principals WHERE principal_id > 256 and type_desc = 'WINDOWS_LOGIN' and name not in ('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators') select 'EXEC sp_addsrvrolemember '''+loginname+''', ''sysadmin''' from syslogins where sysadmin = 1 union select 'EXEC sp_addsrvrolemember '''+loginname+''', ''securityadmin''' from syslogins where securityadmin = 1 union select 'EXEC sp_addsrvrolemember '''+loginname+''', ''serveradmin''' from syslogins where serveradmin = 1 union select 'EXEC sp_addsrvrolemember '''+loginname+''', ''setupadmin''' from syslogins where setupadmin = 1 union select 'EXEC sp_addsrvrolemember '''+loginname+''', ''processadmin''' from syslogins where processadmin = 1 union select 'EXEC sp_addsrvrolemember '''+loginname+''', ''diskadmin''' from syslogins where diskadmin = 1 union select 'EXEC sp_addsrvrolemember '''+loginname+''', ''dbcreator''' from syslogins where dbcreator = 1 union select 'EXEC sp_addsrvrolemember '''+loginname+''', ''bulkadmin''' from syslogins where bulkadmin = 1 ----------------------------------------- ----------------------------------------- select 'Run these after dbs are created:' select ' EXEC sp_defaultdb Ologiname = ''' + name + '''' ,', @defdb = ''' + Coalesce(default_database_name, 'tempdb') + '''' FROM sys.server_principals WHERE principal_id > 256 and type_desc = 'SQL_LOGIN' ------------------------------------------ select ' EXEC sp_defaultdb @loginame = ''' + name + '''' ,', @defdb = ''' + Coalesce(default_database_name, 'tempdb') + '''' FROM sys.server_principals WHERE principal_id > 256 and type_desc = 'WINDOWS_LOGIN' and name not in ('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators'} 

When executed on the source server, the script generates one group of commands to be executed before database deployment, and one group to be executed after the databases are deployed on the target server. The first group re-creates logins and preserves their IDs but not passwords, and then renews their membership in server roles. The second group sets their default databases.

Note 

This method does not preserve passwords for SQL Server logins. You must ask the people to whom these logins are assigned to modify them as soon as possible (using the sp_password stored procedure).

SQL Server also offers the sp_change_users_login procedure. It is designed to manage orphaned users of SQL Server logins. It cannot be used to manage Windows logins. You can use it to display the database users without mapping to server logins:

 exec sp_change_users_login ©Action = 'Report' 

Note 

sp_change_users_login, when used with @Action='Report', does not accept parameters for user or login names.

You can set a SQL Server login manually for a single orphaned user:

 exec sp_change_users_login @Action = 'Update_one',                            @UserNamePattern = 'TomB',                            @LoginName = 'TomB' 

SQL Server can also match all orphaned database users to SQL Server logins with the same name:

 exec sp_change_users_login @Action = 'Auto_Fix',                            @UserNamePattern = '%' ,                            @password = 'myl.password' 

For each user, SQL Server tries to find a SQL Server login with the same name and to set the corresponding sid. If the login already exists, the stored procedure will leave its original password intact. If the matching login does not exist, SQL Server will create it and set its password. The password cannot be left unset or set to null. The person who is actually using the login can later change the password using the sp_password stored procedure.

Tip 

sp_change_users_login with 'Auto_Fix' does a decent job, but the cautious DBA should inspect the results of this operation.