Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Retrieving SQL Server Server Roles for Disaster Recovery


By:   |   Last Updated: 2015-03-22   |   Comments (1)   |   Related Tips: More > Disaster Recovery

Problem

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

Solution

There is an easy way to solve this problem because of a couple of SQL Server security catalog views available to us. We're interested in the following two catalog views:

  • sys.server_principals
  • sys.server_role_members

We'll need to execute two sets of scripts to generate T-SQL to allow us to recover in the event of a instance failure. The first script will ensure all the roles are created. The second script will populate the roles with the appropriate members. If you're thinking about all the steps required for a full recovery, you've probably figured out that we're missing:

  • Ensuring all the logins are present on the new instance.
  • Ensuring any server level permissions assigned to the roles are recreated on the new instance.

These will be tackled in other tips. In this tip we're focusing on the problem of getting all the server roles recreated and all the members repopulated.

Creating User-Defined SQL Server Server Roles

As of SQL Server 2012, it was possible to create roles at the server level. Therefore, if we only script for the traditional fixed server roles, we run the risk of missing a created server role that's important. Here's what we need to execute in order to build the T-SQL that will allow us to recreate any user-defined server roles.

SET NOCOUNT ON;

SELECT 'IF NOT EXISTS(SELECT name 
FROM sys.server_principals 
WHERE type = ''R'' AND name=''' + [name] + ''') 
CREATE SERVER ROLE [' + [name] + '];'
FROM sys.server_principals
WHERE type = 'R'
AND principal_id > 10;

Note that the reason we're filtering based on principal_id is to eliminate the fixed server roles like sysadmin and securityadmin. There's no need to create those roles. Also note that this particular script tests for the existence of the role before creating it. In the event that a role already does exist, we don't want the script erroring out.

Now what to do with this? One thing you can do is put the query into an input file and then use SQLCMD to query the server. If you specify an output file, then you have the T-SQL in a file ready to be run. This is the purpose of the SET NOCOUNT ON; line, for it removes the (# row(s) affected) message that's obviously not T-SQL. An example of the command-line for executing all of this via SQLCMD:

sqlcmd -E -S MySQLServer -i script_create_roles.sql -o recovery_create_server_roles.sql

Of course, it's important in a follow-on step to copy this file off the server where the SQL Server is installed. All of this can be accomplished using a SQL Server Agent job. Here's what the SQLCMD generated script should look like when opened up in SQL Server Management Studio:

Restoring Role Memberships

With all the user-defined roles created (if there were any), all that remains is to ensure all role memberships are properly restored. To do this, we'll use both sys.server_prinicipals and sys.server_role_members:

SET NOCOUNT ON;

SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + p.[name] +
  ''', @rolename = ''' + r.[name] + ''';'
FROM sys.server_principals AS p
  JOIN sys.server_role_members AS srm
    ON p.principal_id = srm.member_principal_id
  JOIN sys.server_principals AS r
    ON srm.role_principal_id = r.principal_id
WHERE p.[name] <> 'sa';

We do have to filter out sa. The reason for this is sa cannot be assigned to a role. It's already a member of the sysadmin server role so there shouldn't be a need to add it to another role. If we try and add sa to a role (even sysadmin) we'll receive an error. Therefore, we'll simply filter it out. If you've renamed the sa account in your installations (a good practice), then modify the script accordingly.

If you're wondering why we're not checking to see if the login is already a member, that's because sp_addsrvrolemember won't error out if the login already has membership. Therefore, we can simply call the stored procedure and be done. Now if you've installed SQL Server 2012 or 2014 and looked at Books Online for sp_addsrvrolemember, you might have seen a note indicating that sp_addsrvrolemember will be removed in a future version of SQL Server and that you should use ALTER SERVER ROLE. Yes, sp_addsrvrolemember is deprecated. However, since sp_addsrvrolemember works with all supported versions of SQL Server at the time this tip was published, I've chosen to use the system stored procedure. The reason is simple: ALTER SERVER ROLE does not work before SQL Server 2012. Many of use still have SQL Servers with versions below SQL Server 2012. If I chose not to use sp_addsrvrolemember in all cases, I'd have to have two sets of scripts.

As far as automation is concerned, you can use the same mechanism you did to restore the roles, for instance, a SQLCMD script run from a SQL Server Agent job. Here's an example of the output opened up in SQL Server Management Studio:

Next Steps


Last Updated: 2015-03-22


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.



    



Tuesday, November 17, 2015 - 9:24:27 AM - HanShi Back To Top

If you modify the WHERE clause in the code for role membership from filtering on [name] to filtering on [principal_id], it don't matter anymore if the 'sa' account is renamed or not. The 'sa' login always has a principal_id of 1.

So instead of:
WHERE p.[name] <> 'sa';

use:
WHERE p.principal_id <> 1;

This way the code can be generally used.
 


Learn more about SQL Server tools