I need to update data (all tables) on our QA environment by replacing it with our latest Prod Database data (Make QA more actual). As I understand, by default if I do it with SSMS it will also replace Security options and Roles which I want to avoid:

  • Our Prod and QA DBs are on different servers so service accounts regulating activity on servers are also different
  • AD groups are different, so if I just restore DB from Prod on QA, I will give access to QA to some users
  • Any other unexpected issue

Is it possible somehow to replace data in all tables (Tables on QA and Prod are identical, difference only in amount of data) from DB backup file and do not touch anything else?

As I understand through Google my only variant is to restore Prod DB on QA server with different name, truncate tables on QA and use SQL MS Wizard but I hope that there are other options.

3rd party compare tools is not an option because server owner is not allow to use it. Only possible options is something free from Microsoft. It's need to be done just one time.

  • You can script all your users and their permissions once and after every restore drop users in restored database and execute your script that creates users and grant them permissions
    – sepupic
    Commented Oct 9, 2019 at 8:20
  • Go from another side - change users instead of changing objects. Commented Oct 9, 2019 at 8:21
  • Create a table with mapping between logins on your test environment and users inside your database. After restoring remap the users to the logins using ALTER USER command. Commented Oct 9, 2019 at 8:30
  • @DenisRubashkin I thought about it, could you describe it a little deeper? You mean run group of Create user x for login y statements? And what about user Membership, Properties and etc? Is it possible to generate script for them in one script as well?
    – The Guy
    Commented Oct 9, 2019 at 8:37
  • There's nothing that comes with SQL Server that will generate those scripts for you, handle SID mapping etc. But you can find such tools out there. SDU tools from SQL Down Under is TSQL based. DBATools.io is powershell based. Those can serve as two starting points, use whichever suits you best. Commented Oct 9, 2019 at 8:46

1 Answer 1


Create a table on your QA environment inside a service database or master. Something like below:

CREATE TABLE [dbo].[SQLAccess](
    [DBName] [sysname] NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [UserName] [sysname] NOT NULL

Fill the table with users and logins that have to be remapped:

INSERT SQLAccess (DBName, LoginName, UserName)
('YourRestoredDBName', 'TestServiceLogin', 'ProdServiceLogin'),
('YourRestoredDBName', 'TestDomain\ADUser', 'ProdDomain\ADUser')
-- and so on

After restoring:

USE [YourRestoredDBName];

DECLARE @SqlStatement NVARCHAR(max)
    , @LoginName    sysname
    , @UserName     sysname

DECLARE MyCursor Cursor
    SELECT LoginName
        , UserName
    FROM YourServiceDBName.dbo.SQLAccess
    WHERE DBName = 'YourRestoredDBName'

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @LoginName, @UserName


    SELECT @SqlStatement = 'ALTER USER [' + @UserName + '] WITH LOGIN = [' + @LoginName + ']'

    EXEC sp_executesql @SqlStatement

    FETCH NEXT FROM MyCursor INTO @LoginName, @UserName

CLOSE MyCursor

As a result you will get a database where your test logins have access to test data the same way as prod logins had on the prod environment.

Of course, it is just a concept, feel free to expand the solution with adding logging, error handling, adding additional roles or permissions for some users, etr.

  • Why do you assume that the principals should be remapped? They can be completely different. The correct way is not to remap, but to drop all the principals from server2 and to recreate all the users that were here in this db before the restore. Second, you did not script any permission
    – sepupic
    Commented Oct 9, 2019 at 9:48
  • Thanks, i get that it is impossible to do it in some other way, only something like you descriped in you answer. I just hoped that i could miss something useful when google it myself((
    – The Guy
    Commented Oct 9, 2019 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.