Moving a database to a new server
When you move a database between SQL Server instances the database-level Users and their permissions will also be moved as this information is stored in database-level system tables.
What isn’t automatically transferred is information about the Users’ parent Logins, so you will need to check whether:
The necessary Logins exist on the new server
The Logins have the correct server-level permissions
The User and Login Security Identifiers (SIDs) match (issue for SQL Authenticated Logins only)
The passwords are correct (issue for SQL Authenticated Logins only)
Scripting Login information
Prior to a database move or a complete server migration/upgrade you’ll want to script out statements to 1) create the logins, 2) add them to any server level roles and 3) grant (or deny) them any additional permissions.
This can be accomplished with the script logins_script_20150813.sql. You can adjust the WHERE clause of the query that populates the @Logins table (see below) to filter for Logins of particular interest. You must execute the entire script as one batch as it uses table variables which have a batch-level scope. Update this portion of the larger script to build statements for specific Logins:
INSERT INTO@Logins(PrincipalId,Name,SID,IsDisabled,Type,PasswordHash )
SELECT p.principal_id,p.name,p.sid,p.is_disabled,p.type,l.password_hash
FROM sys.server_principalsp
LEFTJOINsys.sql_loginsl ONl.principal_id =p.principal_id
WHERE p.typeIN('U','G','S')AND
p.name <>N'sa'AND
p.name NOTLIKEN'##%'AND
p.name NOTLIKEN'distributor_admin'AND
p.name NOTLIKEN'NT [SA]%';
The script only generates the commands you’ll need to recreate the logins on another server. It does not execute any of the commands. It’s very lightweight and will run quickly.
For SQL Authenticated accounts the CREATE LOGIN scripts will include the hashed passwords and SIDs. It’s not possible to un-hash passwords, but this mechanism will transfer the passwords correctly to a new server.
For Windows Authenticated accounts passwords and SIDs are controlled outside of SQL Server. A given account will automatically use the same SID and password across all servers where it exists as a Login.
Applying the Login Scripts
On the destination server only execute the scripted commands that you need. It’s a security risk to grant server-level access to users who do not need it.
If some of the logins already exist on the destination server script them out (using logins_script_2015051.sql) then compare the password hashes, SIDs and permissions with those from the source server. You’ll want to be careful not to break existing functionality on the destination server to accommodate a newly restored database.
Check for Orphaned Users
After adding and updating the Logins execute the script users_orphaned.sqlagainst the newly restored database to check for database Users that don’t have a corresponding Login on the destination server. -- Check for database users without parent logins on the server
SELECT name,type_desc,create_date,modify_date,sid
FROM sys.database_principals
WHERE typeIN('U','G','S')AND
name NOTIN('dbo','guest','INFORMATION_SCHEMA','sys')AND
name NOTIN(SELECT name
FROM sys.server_principals);
You’ll need to create Logins for any orphaned users that are needed for your applications. Consider dropping any Users you determine are no longer needed in the database.
Synchronizing SIDs
As a last step, execute the script users_logins_sync_SIDs.sql against the newly restored database to check for database users having SIDs that differ from those of their corresponding Logins. The script assumes the Logins and Users will have matching names. This is typical, but not required. SELECT l.name ASLoginUserName,l.sidASLoginSID,u.sidASUserSID,
'ALTER USER '+QUOTENAME(u.name)+' WITH LOGIN = '+
QUOTENAME(l.name)+';'ASSyncCmd
FROM sys.server_principalsl
INNERJOINsys.database_principalsu ONu.name =l.name
WHERE l.type='S'AND
u.sid<>l.sid;
The script will generate the ALTER USER statements necessary to update the Users’ SIDs to match those of their corresponding Logins. Review the statements for correctness before executing them.
