Browse: Wetory / Ideas / SQL Server / Databases / Database restore – preserve permissions

Database restore – preserve permissions

by Tomáš RybnickýNo Comments Reading Time: 2 minutes

I’ve made some updates to standardized database restore solution. Latest version is already available on my GitHub profile. New feature is preserving permissions settings after database restore. Your current database security settings can be the same in your restored database. Which means that you don’t need any extra post-restore steps with granting permissions anymore. This is useful when database restores run on regular basis, ex.: PROD to DEV etc.

Database security #

What security related settings we have in database?

All above mentioned settings come with database during restore as it is stored in system catalogues specific to particular database. And because of that you have to take care of “recreating” those settings after every restore. You can achieve this by running some T-SQL or clicking within Management Studio. If you have some SQL Agent jobs for such restores then you have additional steps for fixing permissions. This is fundamental for proper communication between application and your newly restored database.

Procedure now also covers problem with orphaned users which is pretty common after restoring database to another instance. Every users that existed in original database is re-created after restore and mapped with login using right SID. 

Workflow #

I would like to describe what is happening within stored procedure in following steps. You can always go and check procedure code and go through it. So lets take it as some sum up. This is adding simple few steps to restore process.

  1. Create temporary tables for data collection
  2. Gather current database security info in collecting data phase
    1. Database principals – users and roles (roles need to be created in right order)
    2. Schemas ownership
    3. Role members mapping
    4. Explicit permissions over securables within database
    5. Extended permissions for principals
  3. Iterating table rows in temporary tables and processing it in right order
    1. Create principal
    2. Add membership, if any
    3. Add explicit permissions, if any
    4. Add extended properties, if any
  4. Command execution is done via Ola’s CommandExecute procedure so if you are using @LogToTable parameter, you can find command tracking in CommandLog table within master database.
  5. Additional cleanup at very end of execution

And that’s all. Order of creating database principals requires some “advanced” logic, but the rest of script is “simple” T-SQL approach.

New parameter #

By default any of above steps happen. You need to override default value of new parameter @PreservePermissions by setting it to ‘Y’ which obviously means YES. Information updated in original post as well.

EXEC [master].[dbo].[RestoreDatabase]
@BackupFile = N'\\Path\To\BackupFile\Backup.bak',
@Database = N'TestDB',
@LogToTable = 'Y',
@PreservePermissions = 'Y'

Discussion #

Hopefully new parameter will be helpful for your scenarios. I’ve tested it on various environments, but… 🙂 If you have any trouble with this procedure please let me know using below comments section or Issues section in GitHub repository.

Leave a Reply