User Access Automation during database restoration

I would like to share user access automation scripts during Database restoration I have performed earlier this year. This script is most customized for user access management when database is restored from production to Dev/UAT environment.

We restore multiple databases from production environment into Dev/Test/UAT every week (We also trim sensitive data ). All Developers and Testers lose access immediately after restoration. Started with manually managing access, later created a script to provide access but every other week I had to edit the script for new users.

I come up with 2 scripts, first one will take the backup of users in Dev/Test environment in DatabaseAccessTracker table prior to Database restoration and second script uses the backup and provides access after database restoration. I have been using these scripts for at least 6 months and is considerably stable. At least in my environment 

1. Capture
SP_CaptureUserAccess is the procedure which takes user access information into DatabaseAccessTracker table.

2. Restore
SP_RestoreUserAccess is the procedure which restored missing user access using DatabaseAccessTracker table data.

You may download the script from:

-- --Example of usage

--Capture Access 
exec master..SP_CaptureUserAccess @AdminDatabase='admindb', @ActionDatabase='adventureworks'

--Restore access
exec master..SP_RestoreUserAccess @AdminDatabase='admindb', @ActionDatabase='adventureworks'

-- @AdminDatabase is used to store DatabaseAccessTracker table, you may use different database.
-- @ActionDatabase is the database which will be restored from different environment.

Please share if you find any bugs!

This entry was posted in Script, SQL and tagged , , , . Bookmark the permalink.

1 Response to User Access Automation during database restoration

  1. Pingback: Avoid Blunders with SQL Query execution – Check where you are running the script | Blog for reference – Vijred

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s