SQL Server Save and Restore Permissions – How to Guide After Database Refresh

permissionssql server

A database in a STAGING environment is overwritten every N weeks with a PRODUCTION copy that is more recent.

What is the proper way to save and then restore users, roles and explicitly granted permissions as they were before the existing database was overwritten by the production copy?

I do not want to restore users and permissions as they were in PRODUCTION, but as they were in the DEV environment before the database was overwritten.

This refresh process must be automated as there are many different environments with dozens of instances that can potentially host up to 10 databases.

Best Answer

@AaronBertrand is right. You should consider a different method of refreshing your staging environment. If for no other reason that any sensitive data that you have in production is now available to a new group of users who may not be as carefully restricted as in production. However I realize that changing your process can take time (if management will even approve the time).

Essentially you want to create a script that will drop all of your existing permissions from the copy of the prod database and another that will generate all of the permissions that used to be there. My personal preference is going to be to use a script like sp_dbpermissions. Run it on your production environment and then copy the drop user script column (second to last column) into a .sql file and save it. Be very careful not to run it in production of course.

EXEC sp_dbpermissions 'dbName'

enter image description here

You only need the second to last column from the first result set.

Next run the same script on your Staging database. This time you will want to copy the last column from each of the three result sets. This will give you the CREATE USER, sp_AddRoleMember and GRANT/DENY commands to add the permissions for this database. Again save them in a .sql file. After your restore go ahead and run the first script (to remove the old permissions) then the second (to add the new).

I also highly recommend taking a backup of your Staging database before overwriting it. This way if there is a problem you can always restore it back (even if to an alternate location) and confirm any missing permissions.

Related Solutions

SQL Server Security – Restricting Users to COPY ONLY Backups

You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions.

e.g.

USE test_kin
GO
CREATE ROLE [deny_log_backups]
GO
USE [test_kin]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
GO
ALTER USER [Kin] WITH DEFAULT_SCHEMA=[dbo]
GO
use test_kin
GO
DENY BACKUP LOG TO [deny_log_backups]
GO
USE test_kin
GO
EXEC sp_addrolemember N'deny_log_backups', N'kin'
GO

Now test it :

backup database [test_kin]
to disk = 'C:\crap_test\kin_test_full.bak'
with compression, stats =10, init

---- ### success for FULL BACKUP 

backup log [test_kin]
to disk = 'C:\crap_test\kin_test_log.log'

 --- $$$ ERROR MESSAGE 

Msg 262, Level 14, State 1, Line 3
BACKUP LOG permission denied in database 'test_kin'.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

Sql-server – What’s the best method to refresh only a few tables within a test database from production

There are 2 methods that will suit your needs :

(Note: If the tables are referenced by foreign key, then you won't be able to use TRUNCATE. You have to delete in chunks. Alternatively, you can drop all indexes + Foreign keys and load data and then recreate them).

BCP OUT and BULK INSERT INTO destination database.

Make sure you put the test database in simple/bulk-logged recovery mode.

Enable Trace Flag 610 - minimally logged inserts into indexed tables.

/************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another*********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         



--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
        +  QUOTENAME(name) 
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','') 
        +'.dat'' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',  
        TABLOCK
        )'  + char(10) 
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id)

Method 2 : SSIS - My preferred method in this case.
- No staging to disk required. All processing is done in memory.
- You can schedule the SSIS package using sql agent job every month to automate the refresh of tables from PROD to TEST server.
- Choose the "FAST LOAD" option
- Make sure you choose a good rows per batch number (If you choose too high,there will be lock escalation - keep it lower than 5K)

Reference : The Data Loading Performance Guide and my answer for - Insert into table select * from table vs bulk insert

Best Answer

Related Solutions

SQL Server Security – Restricting Users to COPY ONLY Backups

Sql-server – What’s the best method to refresh only a few tables within a test database from production

Related Question