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
SP_CaptureUserAccess is the procedure which takes user access information into DatabaseAccessTracker table.
SP_RestoreUserAccess is the procedure which restored missing user access using DatabaseAccessTracker table data.
You may download the script from: https://onedrive.live.com/?cid=4A5897AB0DE9AFDC&id=4A5897AB0DE9AFDC%214347
-- --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!