Traditionally when doing a restore, moving a database, etc. a DBA would simply go into Management Studio and use the Generate Scripts wizard to script all logins and permissions. This approach can take several minutes and as I recently learned does not script database level permissions.
Bothered by the amount of effort and the fact that I kept forgetting to script out the permissions until just after I had wiped them out by starting a restore, I set out to create scripts that I could just include as steps in my restore jobs. Given that the restore wipes out the database, I knew I had to have 2 steps. The first step stores the permissions before the restore, while the second puts them back after. In the spirit of keeping the restore jobs simple, I wrapped up all of the logic into 2 stored procedures that do not require any arguments.
The first stored procedure, sp_dba_StoreDatabasePermissions, stores the logins roles and permissions to a table in msdb. I use msdb here because everybody has it and it is not master. The table is named for the database it corresponds to followed by ‘_permission_store’. The permission store table has 2 columns, the first is the actual SQL command while the second is the order to run it in. The ordering is done in groups, with all roles ranked to be run first, followed by users, adding users to roles and finally the permissions that correspond to the users and roles. The stored procedure makes use of a synonym to point to the permission store, cutting down on the use of dynamic SQL and enhancing readability. The logic to get the permissions is based on the logic of a script by Narayana Vyas Kondreddi.
The second stored procedure in the pair, sp_dba_GetDatabasePermissions, is very simple. First it checks for the synonym in the database and creates it if it is missing. After verifying the synonym, the stored procedure opens up a cursor against the permission store and begins executing commands ordered ascending by run_order. After running all commands, the stored procedure checks the value of the optional parameter @keep_permission_store to see if it should clean up the permission store table or leave it out there. The default behavior is to drop the permission store when done with it.
It seems like every day I find more uses for these stored procedures. Most recently I have been running them at replication subscribers before making any changes at the publisher. The @keep_permission_store flag of sp_dba_GetDatabasePermissions comes in really handy here. Passing a value of 1 allows permissions to be put back several times, a lifesaver when things do not go right on the first try.
I also use these stored procedures to copy permissions between databases on different servers. I started out running sp_dba_StoreDatabasePermissions then selecting from the permission store table ordered by rank in text, copying, pasting and running on the new server. After adding the print statement to sp_dba_GetDatabasePermissions it was just easier to call sp_dba_StoreDatabasePermissions then sp_dba_GetDatabasePermissions right away, copying the commands from the messages window and pasting them into a query window to run wherever I want.
These stored procedures have served me well and I hope they will serve you well. I would love to hear how people are using them and any suggestions for enhancements. As always, scripts on the internet are like Halloween candy, check them thoroughly before consuming.