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
- Read about how to audit for user-defined server roles, including their permissions.
- Read these additional tips related to security.
- Read this tip on understanding SQL Server fixed server roles.
- Read this tip on retrieving your database permissions for disaster recovery.
