Retrieving SQL Server Fixed Database Roles for Disaster Recovery

By:   |   Comments (4)   |   Related: > Security


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?


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):

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;

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley 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

Comments For This Article

Wednesday, March 9, 2011 - 10:29:46 AM - K. Brian Kelley Back To Top (13157)

pl80, the script that's in the tip covers SQL Server 2000 as well (which is still supported by Microsoft). The sys.database_principals catalog view doesn't exist until SQL Server 2005.

Wednesday, March 9, 2011 - 10:28:29 AM - K. Brian Kelley Back To Top (13156)

If you're talking about disaster recovery, likely you don't have databases to attach because the original equipment went down in the disaster. So now you're reliant on the backups. One advantage to having the script is if your more recent backups fail, but you have older backups that are still relevant schema wise, you can get back to where the app works. By scripting out the roles (and having them written to different tapes/locations than the backups), you're able to get security back to the most recent scripting time. So then you're only talking about data loss as opposed to data loss + trying to figure out how to get everyone working again during a disaster recovery operation. Since this takes so little to do, there's no reason not to do it.

Wednesday, March 9, 2011 - 5:30:53 AM - Willem G Back To Top (13155)

Maybe I'm overlooking something, but if you simply script out all your logins with sp_help_revlogin, it seems to me you have everything you need and recreate logins while maintaining sids/passwords very easily.

You can create a new server if necessary, recreate the logins with the output from sp_help_revlogin and attach the databases or load them from backup. Database roles will remain in place, and logins/users will be in sync.

Friday, February 11, 2011 - 8:57:05 AM - pl80 Back To Top (12916)

Simpler script (and yes, you can have fixed roles only).  In the last column there are statements to apply the permission:

select as RoleName, as UserName,
   exec sp_addrolemember [' + + '], [' + + ']
  end' as Query
 from sys.database_role_members drm
   inner join sys.database_principals dpr on drm.role_principal_id = dpr.principal_id
   inner join sys.database_principals dpu on drm.member_principal_id = dpu.principal_id
 where <> 'dbo'
 and dpr.is_fixed_role = 1

get free sql tips
agree to terms