Retrieving SQL Server Fixed Database Roles for Disaster Recovery

By: K. Brian Kelley   |   Comments (4)   |   Related: > Security

Problem

We ran into a case recently where we had the logins and users scripted out on my SQL Server instances, but we didn't have the fixed database roles for a critical database. As a result, our recovery efforts were only partially successful. We ended up trying to figure out what the database role memberships were for that database we recovered but we'd like not to be in that situation again. Is there an easy way to do this?

Solution

There is and it revolves around the system stored procedure sp_helprolemember (not sp_helpsrvrolemember, which is for fixed server roles). If we look at the entry for this system stored procedure in the online version of Books Online, we can get the output format, which gives us what we need to create a temporary table to hold our results. This leads to our CREATE TABLE statement (with a SET NOCOUNT ON to suppress row count numbers coming back, because we don't need them):

SET NOCOUNT ON;
CREATE TABLE #DatabaseRoleMembers (
    DbRole sysname,
    MemberName sysname,
    MemberSID VARBINARY(85)
);

We can use an INSERT statement along with a stored procedure execution, and that's what we'll do with sp_helprolemember. So if we want to cover all known fixed database roles, here's what we do:

INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_owner';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_securityadmin';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_accessadmin';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_backupoperator';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_ddladmin';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_datareader';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_datawriter';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_denydatareader';
INSERT INTO #DatabaseRoleMembers (DbRole, MemberName, MemberSID)
EXEC sp_helprolemember 'db_denydatawriter';

Like with sp_helpsrvrolemember, sp_helprolemember can be executed without a parameter. In this case it will return every database role that has at least one member, including user-defined database roles. In this tip, we're simply just trying to get the fixed database roles, and the only way to do that is by specifying each role separately. If what you really need is members of all roles in the database, then don't execute all of these separately but instead simply execute sp_helprolemember without the parameter.

With our results in a temporary table, we just need to write a SELECT query that builds our T-SQL to re-create the role memberships at a later time (and then drop the temporary table):

SELECT 'EXEC sp_addrolemember @rolename = ''' + DbRole + ''', @membername = ''' + MemberName + ''';'
FROM #DatabaseRoleMembers
WHERE MemberName <> 'dbo';
DROP TABLE #DatabaseRoleMembers;
GO 

Note the WHERE clause on the query. The reason for this is that you cannot place the dbo user in any database roles. By rule it is a member of db_owner and this will be returned in the query if we don't explicitly exclude dbo. All of this should be run as a single batch script and the output should be saved off to a file which will be backed up. This will ensure we have a runnable script for future recovery.

Now if you're wondering about why the choice of sp_helprolemember, it's simple: it runs across all supported versions of SQL Server without a version check. We could use catalog views and the like, but since sp_helprolemember is still fully supported, we can use it to create a very simple solution.

Next Steps

• Read these additional tips related to security.
• Read this tip on retrieving your database permissions for disaster recovery.
• Read this tip on understanding SQL Server fixed database roles.
• Add this process to your disaster recovery procedures.  Along with having database backups, having scripts to recreate permissions may come in handy.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips