Database Refresh Sync
This script is useful when restoring\refreshing a database and you want to maintain the users, roles, and role members that existed before the refresh. This is extremely useful when refreshing a dev\test database from production because dev\test databases may have additional users and existing users may have different role memberships. This script will provide, with just a few clicks, a method of identify all users, roles, and role memberships that existed in the refreshed database before the refresh and allow you to add them back after the refresh. The script will also help with the following:
1. Re-sync the SIDs for any users that have a login on the refreshed server but whose SIDs do not match.
2. Help you identify any new users or roles that exists after the refresh.
3. Provide a list of new users that do not have matching logins on the refreshed server.
There are a lot of comments throughout the code that help explain how to use the script. The following is taken directly from the beginning of the script and describes the purpose and how to use the script:
This script will aid in restoring the different/special permissions that exists in a dev/test database
after a refresh of the database from a different source, such as production. The script will help
identify users, roles, and role members that existed in the database BEFORE the refresh and it will
help create scripts that you will run to re-create all of those users, roles, and role members that
no longer exists after the refresh. The final results will also help you re-sync users with logins
where the SID for the user and the login, on the refreshed server, do not match. There will also
be help, and instructions for creating logins for any users that do not have an associated login
on the refreshed server.
To use this script, do the following:
1. Run this script, in the database that is going to be refreshed, BEFORE THE REFRESH.
2. Copy the results to a new query window on the same server where the refresh is taking place.
3. Restore\refresh your database
4. Run the script that you copied in step #2 above. This will produce five result sets:
a. The first result set is a script that you will paste into a new query window, on the
server you where you just refreshed your database.
b. The second result set will list any database roles that are now in the refreshed database
that were not there before the refresh. (This result set is for reference.)
c. The third result set will list any users that are now in the refreshed database that were
not there before the refresh. (This result set is for reference.)
d. The fourth result set will list any database role members that are now in the refreshed
database that were not there before the refresh. (This result set is for reference.)
e. The fifth result set will list any users in the refreshed database for which no login
exists on the server. The script that creates this result set includes information
about correcting this.
5. Run the script that you just copied in step #4a above adn everything will be re-synced except
for the users without associated logins. You will have to handle that separately.
IMPORTANT: There is one issue that will arise if the collation on your database is different than
the collation of your SQL Server. There are two locations where an error will appear
when you run the script in step #4. If this happens, simply click on the errors in the
Messages window to go to the point of the errors and make the following changes:
Error 1: Change: SET @txt = 'WHERE name IN (SELECT name FROM sys.server_principals) '
To: SET @txt = 'WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT name FROM sys.server_principals) '
(where the collation matches the collation of the SQL Server)
Error 2: Change: SET @txt = 'AND name NOT IN (SELECT name FROM sys.server_principals) '
To: SET @txt = 'AND name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT name FROM sys.server_principals) '
(where the collation matches the collation of the SQL Server)
/*
This script will aid in restoring the different/special permissions that exists in a dev/test database
after a refresh of the database from a different source, such as production. The script will help
identify users, roles, and role members that existed in the database BEFORE the refresh and it will
help create scripts that you will run to re-create all of those users, roles, and role members that
no longer exists after the refresh. The final results will also help you re-sync users with logins
where the SID for the user and the login, on the refreshed server, do not match. There will also
be help, and instructions for creating logins for any users that do not have an associated login
on the refreshed server.
To use this script, do the following:
1. Run this script, in the database that is going to be refreshed, BEFORE THE REFRESH.
2. Copy the results to a new query window on the same server where the refresh is taking place.
3. Restore\refresh your database
4. Run the script that you copied in step #2 above. This will produce five result sets:
a. The first result set is a script that you will paste into a new query window, on the
server you where you just refreshed your database.
b. The second result set will list any database roles that are now in the refreshed database
that were not there before the refresh. (This result set is for reference.)
c.The third result set will list any users that are now in the refreshed database that were
not there before the refresh. (This result set is for reference.)
d.The fourth result set will list any database role members that are now in the refreshed
database that were not there before the refresh. (This result set is for reference.)
e.The fifth result set will list any users in the refreshed database for which no login
exists on the server. The script that creates this result set includes information
about correcting this.
5. Run the script that you just copied in step #4a above adn everything will be re-synced except
for the users without associated logins. You will have to handle that separately.
IMPORTANT: There is one issue that will arise if the collation on your database is different than
the collation of your SQL Server. There are two locations where an error will appear
when you run the script in step #4. If this happens, simply click on the errors in the
Messages window to go to the point of the errors and make the following changes:
Error 1:Change: SET @txt = 'WHERE name IN (SELECT name FROM sys.server_principals) '
To:SET @txt = 'WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT name FROM sys.server_principals) '
(where the collation matches the collation of the SQL Server)
Error 2:Change:SET @txt = 'AND name NOT IN (SELECT name FROM sys.server_principals) '
To:SET @txt = 'AND name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT name FROM sys.server_principals) '
(where the collation matches the collation of the SQL Server)
Take the time to read the comments in each resulting script as those will help identify exactly what is
being done in each part of the script.
*/
DECLARE @txtvarchar(8000)
CREATE TABLE #missing_roles(
namesysname)
CREATE TABLE #missing_users(
namesysname,
typechar(1),
type_descvarchar(30))
CREATE TABLE #original_roles(
namesysname)
CREATE TABLE #original_users(
namesysname,
typechar(1),
type_descvarchar(30))
CREATE TABLE #output(
recint IDENTITY(1,1),
txtvarchar(8000))
-- Get all of the current (original) users and roles in the database before it is refreshed
INSERT INTO #original_roles
SELECT name
FROM sys.database_principals
WHERE type = 'R'
INSERT INTO #original_users
SELECT name, type, type_desc
FROM sys.database_principals
WHERE type <> 'R'
SET @txt = 'USE master'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'GO'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
-- Setup a list of all of the current/original roles and users to be reviewed after the refresh
SET @txt = '/*'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'Database Name: ' + db_name()
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'This is the original list of roles, users, and database role members in the database before the refresh:'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DATABASE ROLES'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
INSERT INTO #output(txt)
SELECT name
FROM #original_roles
ORDER BY name
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DATABASE USERS'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
INSERT INTO #output(txt)
SELECT name + ' - ' + type_desc + ' (' + type + ')'
FROM #original_users
ORDER BY type, name
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DATABASE ROLE MEMBERS'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
INSERT INTO #output(txt)
SELECT b.name + ' - ' + c.name
FROM sys.database_role_members a JOIN sys.database_principals b ON a.role_principal_id = b.principal_id
JOIN sys.database_principals c ON a.member_principal_id = c.principal_id
ORDER BY b.name, c.name
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '*/'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Now we are going to get into comparing what was in the database before the refresh to what is in the database now.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- First we need to get a list of all of the roles, users, and database role members from before the refresh'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- and store that data in some temp tables to be used for comparison. You can also query these tables at '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- while you are working on this.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #original_roles('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'namesysname)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #original_users('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'namesysname,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'typechar(1),'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'type_descvarchar(30))'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #original_role_members('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'role_namesysname,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'user_namesysname)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
INSERT INTO #output(txt)
SELECT 'INSERT INTO #original_roles VALUES (''' + name + ''')'
FROM sys.database_principals
WHERE type = 'R'
ORDER BY name
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
INSERT INTO #output(txt)
SELECT 'INSERT INTO #original_users VALUES(''' + name + ''', ''' + type + ''', ''' + type_desc + ''')'
FROM sys.database_principals
WHERE type <> 'R'
ORDER BY name
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
INSERT INTO #output(txt)
SELECT 'INSERT INTO #original_role_members VALUES(''' + b.name + ''', ''' + c.name + ''')'
FROM sys.database_role_members a JOIN sys.database_principals b ON a.role_principal_id = b.principal_id
JOIN sys.database_principals c ON a.member_principal_id = c.principal_id
ORDER BY b.name, c.name
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Now we will collect the current data to compare against the original.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'USE [' + DB_NAME() + ']'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'GO'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #current_roles('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'namesysname)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #current_users('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'namesysname,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'typechar(1),'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'type_descvarchar(30))'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #current_role_members('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'role_namesysname,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'user_namesysname)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #current_roles'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name FROM sys.database_principals WHERE type = ''R'' ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #current_users '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name, type, type_desc FROM sys.database_principals WHERE type <> ''R'' ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #current_role_members '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT b.name, c.name FROM sys.database_role_members a JOIN sys.database_principals b ON a.role_principal_id = b.principal_id JOIN sys.database_principals c ON a.member_principal_id = c.principal_id ORDER BY b.name, c.name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Now we will run comparisons and create the scripts needed to correct the differences.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find any roles that were in the original database that are no longer in the database after the refresh.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #create_role('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'recint IDENTITY(1,1),'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'namesysname NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'cmdvarchar(1000))'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_role(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''USE ' + db_name() + ''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_role(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_role(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_role(name, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name, ''CREATE ROLE ['' + name + ''] AUTHORIZATION [dbo]'' '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #original_roles '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE name NOT IN (SELECT name FROM #current_roles) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_role(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_role(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_role(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find any users that were in the original database that are no longer in the database after the refresh.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #create_user('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'recint IDENTITY(1,1),'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'namesysname NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'typechar(1) NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'type_descchar(30) NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'cmdvarchar(1000))'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_user(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''USE ' + db_name() + ''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_user(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_user(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_user(name, type, type_desc, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name, type, type_desc, ''CREATE USER ['' + name + ''] FOR LOGIN ['' + name + ''] WITH DEFAULT_SCHEMA=[dbo]'' '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #original_users '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE name + type + type_desc NOT IN (SELECT name + type + type_desc FROM #current_users) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_user(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_user(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #create_user(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find the database role members that were setup before the refresh that are no longer setup after the refresh.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #add_role_member('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'recint IDENTITY(1,1),'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'role_namesysname NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'user_namesysname NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'cmdvarchar(1000))'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #add_role_member(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''USE ' + db_name() + ''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #add_role_member(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #add_role_member(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #add_role_member(role_name, user_name, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT role_name, user_name, ''EXEC sp_addrolemember '''''' + role_name + '''''', '''''' + user_name + '''''''' '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #original_role_members '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE role_name + user_name NOT IN (SELECT role_name + user_name FROM #current_role_members) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY role_name, user_name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #add_role_member(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #add_role_member(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #add_role_member(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find any users currently in the database that DO HAVE A MATCHING LOGIN ON THE SERVER but whose SIDs do not match.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- These users will have to the sp_change_users_login script run for them to match up the SIDs.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #change_users_login('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'recint IDENTITY(1,1),'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'namesysname NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'cmdvarchar(1000))'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #change_users_login(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''USE ' + db_name() + ''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #change_users_login(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #change_users_login(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #change_users_login(name, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name, ''EXEC sp_change_users_login ''''UPDATE_ONE'''', '''''' + name + '''''', '''''' + name + '''''''' '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM sys.database_principals '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE name IN (SELECT name FROM sys.server_principals) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'AND sid NOT IN (SELECT sid FROM sys.server_principals) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'AND name <> ''public'''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #change_users_login(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(''GO'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #change_users_login(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #change_users_login(cmd)'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES('''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- The following will create the script that you will need to run on the '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- server where you just restored the database.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- You do not have to set the database. That is already done in the script.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'CREATE TABLE #run_script('
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'recint NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'role_namesysname NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'user_namesysname NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'typechar(1) NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'type_descchar(30) NULL,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'cmdvarchar(1000))'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(1, ''-- Run the following on the server where you just restored the database.'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(2, ''-- These commands will add back any roles, users, and role memberships that'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(3, ''-- existed before the restore. The final command will also re-sync any users'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(4, ''-- whose name matches but whose SID does not match with the login of the same'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(5, ''-- name on the server.'')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'VALUES(6, '''')'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DECLARE @max_rec_create_role' + CHAR(9) + CHAR(9) + 'int,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = CHAR(9) + CHAR(9) + '@max_rec_create_user' + CHAR(9) + CHAR(9) + 'int,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = CHAR(9) + CHAR(9) + '@max_rec_add_role_member' + CHAR(9) + 'int,'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = CHAR(9) + CHAR(9) + '@max_run_script' + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + 'int'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT @max_rec_create_role = MAX(rec) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #create_role'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT @max_rec_create_user = MAX(rec) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #create_user'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT @max_rec_add_role_member = MAX(rec) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #add_role_member'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT @max_run_script = MAX(rec) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #run_script'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT rec + @max_run_script AS rec, name AS role_name, NULL AS user_name, NULL AS type, NULL AS type_desc, cmd '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #create_role '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'UNION ALL '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT rec + @max_run_script + @max_rec_create_role AS rec, NULL AS role_name, name AS user_name, type, type_desc, cmd '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #create_user'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'UNION ALL '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT rec + @max_run_script + @max_rec_create_role + @max_rec_create_user AS rec, role_name, user_name, NULL AS type, NULL AS type_desc, cmd '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #add_role_member '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'UNION ALL '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT rec + @max_run_script + @max_rec_create_role + @max_rec_create_user + @max_rec_add_role_member AS rec, NULL AS role_name, name AS user_name, NULL AS type, NULL AS type_desc, cmd '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #change_users_login '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY rec'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT MAX(rec) + 1, '''''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #run_script'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT MAX(rec) + 1, ''USE master'''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #run_script'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT MAX(rec) + 1, ''GO'''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #run_script'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'INSERT INTO #run_script(rec, cmd) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT MAX(rec) + 1, '''''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #run_script'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT * '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #run_script '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY rec'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find the database roles that are in the current database that were not there before the refresh.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- This is mainly for reference. If it is from Production, chances are it should stay. Just use this info as needed.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #current_roles '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE name NOT IN (SELECT name FROM #original_roles) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find the database users that are in the current database that were not there before the refresh.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- This is mainly for reference. If it is from Production, chances are they should stay. Just use this info as needed.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name, type, type_desc '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #current_users '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE name + type + type_desc NOT IN (SELECT name + type + type_desc FROM #original_users) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find the database role members that are in the current database that were not there before the refresh.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- This is mainly for reference. If it is from Production, chances are they should stay. Just use this info as needed.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT role_name, user_name '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM #current_role_members '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE role_name + user_name NOT IN (SELECT role_name + user_name FROM #original_role_members) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY role_name, user_name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- Find any users that are currently in the database but for which an associated login does not exist on the server. '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- You will need to add these logins, if they are needed in this environment. How you add them is up to you. If they '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- are Windows Users, you can just add the login and they will automatically sync up to the database users because the SIDs '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- will automatically match. If they are SQL logins, you will need to run the create the SQL login in the way you choose. '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- You can script the login from the source server then create the login on the refreshed server. If you do this, the SIDs will '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- match up and you will not have to do anything else. You could also just create the SQL login on the refreshed server then run '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- the sp_change_users_login to re-sync the SIDs. '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- VERY IMPORTANT: WHEN SETTING UP A NEW LOGIN, MAKE SURE THEIR DEFAULT DATABASE IS A DATABASE TO WHICH THEY HAVE ACCESS, '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = '-- OTHERWISE, THEY WILL GET LOGIN FAILURES WHEN THEY TRY TO CONNECT.'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'SELECT name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'FROM sys.database_principals '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'WHERE type <> ''R'' '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'AND name NOT IN (SELECT name FROM sys.server_principals) '
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'ORDER BY name'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'USE master'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'GO'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #original_roles'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #original_users'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #original_role_members'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #current_roles'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #current_users'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #current_role_members'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #create_role'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #create_user'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #add_role_member'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #change_users_login'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'DROP TABLE #run_script'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'USE master'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = 'GO'
INSERT INTO #output(txt)
VALUES(@txt)
SET @txt = ''
INSERT INTO #output(txt)
VALUES(@txt)
SELECT *
FROM #output
ORDER BY rec
DROP TABLE #missing_roles
DROP TABLE #missing_users
DROP TABLE #original_roles
DROP TABLE #original_users
DROP TABLE #output
GO
USE master
GO 
