Retrieving SQL Server Fixed Database Roles for Disaster Recovery

By:   |   Updated: 2011-02-04   |   Comments (4)   |   Related: More > 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


Last Updated: 2011-02-04


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, March 09, 2011 - 10:29:46 AM - K. Brian Kelley Back To Top

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 09, 2011 - 10:28:29 AM - K. Brian Kelley Back To Top

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 09, 2011 - 5:30:53 AM - Willem G Back To Top

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

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

select
  dpr.name as RoleName,
  dpu.name as UserName,
  'begin
   exec sp_addrolemember [' + dpr.name + '], [' + dpu.name + ']
  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
  dpu.name <> 'dbo'
 and dpr.is_fixed_role = 1



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools