By: Tim Ford | Comments | Related: > Security
Problem
We've all faced this issue: a cursory review of a SQL Server instance we manage appears to have logins assigned to multiple server roles whose rights supercede or overlap one-another. I'm specifically referring to the situation where a login is a member of the sysadmins server role and another lesser server role such as dbcreators, bulkadmin, and so forth.
Now, while I don't recommend doling out sysadmin role membership like it's last months leftover Easter candy I also know that it does happen. So is there an easy way to determine situations on your managed SQL Server instances where a login has membership in the sysadmin role and any of the other server-level roles? Certainly. Without opening the Properties window for each server role and jotting down a listing of logins and comparing by hand? Of course... In this tip I will show you an easy way to get this information using some T-SQL commands.
Solution
This tip is specifically aimed at releases of Microsoft SQL Server since (and including) SQL 2005. The query I'll be presenting involves two System Catalog Views: master.sys.server_principals and master.sys.server_role_members. The sys.server_principals view includes a row for each login and server role on the instance. They are assigned into one of the followinig categories:
SELECT DISTINCT SP.[type], SP.[type_desc]
FROM sys.[server_principals] SP
Meanwhile, the membership in each role is handled through a relationship between sys.server_principals and the sys.server_role_members view. This view simply handles the recursive relationship of sys.server_principals back to itself. Below is a sampling of the data that populates sys.server_role_members:
SELECT TOP 10 * FROM sys.[server_role_members] AS SRM
This information doesn't really tell us much as is, but if we reconcile the key values in this view back to the sys.server_principals view we get a better understanding for what this view is presenting to us:
SELECT TOP 10 SPMember.name AS [login name], SPRole.name AS [role name]
FROM master.sys.server_principals SPRole
INNER JOIN master.sys.server_role_members SRM ON SPRole.principal_id = SRM.role_principal_id
INNER JOIN master.sys.[server_principals] AS SPMember ON SRM.[member_principal_id] = SPMember.principal_id
Now, with a better understanding for what these catalog views contain and represent, a query that provides a listing of logins that have membership in the sysadmin server role and any other server level role can be crafted quite easily. It is just the matter of determining logins that have membership in more than a single server level role and then further qualify the results to only return rows where one of those roles is sysadmins:
--------------------------------------------------------------------------------------
/*
Determine overlapping server role memberships in
SQL Server 2005 and SQL Server 2008
Author: Timothy Ford
http://thesqlagentman.com
*/
--------------------------------------------------------------------------------------
SELECT SPMember.[login name], SPRole.name AS 'role name'
FROM master.sys.server_principals SPRole
INNER JOIN master.sys.server_role_members SRM ON SPRole.principal_id = SRM.role_principal_id
INNER JOIN
(
SELECT SP.name AS 'login name', SP.principal_id
FROM master.sys.server_principals sp_roles
INNER JOIN master.sys.server_role_members SRM ON sp_roles.principal_id = SRM.role_principal_id
INNER JOIN master.sys.server_principals SP ON SRM.member_principal_id = SP.principal_id
WHERE sp_roles.type_desc = 'SERVER_ROLE' --AND SP_roles.name <> 'sysadmin'
GROUP BY SP.name, SP.principal_id
HAVING COUNT(SP.name) > 1
) AS SPMember ON SRM.member_principal_id = SPMember.principal_id
ORDER BY SPMember.[login name]
In the case of my local test server you'll see that I have situations where I can revoke membership to the DBA and GeddyL logins. Of course I should also verify if these logins require sysadmin role membership to begin with, but I leave that to your discretion.
Next Steps
- More tips from the author are available via this link.
- Learn about additional auditing tips at MSSQLTips.com
- The author blogs about SQL Server topics at his personal blog: thesqlagentman.com.
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
