A community of more than 1,600,000 database professionals and growing

Database Refresh Sync

Jason Givens from SQLServerCentral.com

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)

More »