Identifying SQL Server logins with overlapping server roles


By:   |   Updated: 2010-04-23   |   Comments   |   Related: More > 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
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

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

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  
qualify the results to only return rows where one of those roles is sysadmins

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.nameSP.principal_id
   
HAVING COUNT(SP.name) > 1
   
AS SPMember ON SRM.member_principal_id SPMember.principal_id
ORDER BY SPMember.[login name]  
I can revoke membership to the DBA and GeddyL logins

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


Last Updated: 2010-04-23


get scripts

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips
Related Resources




More SQL Server Solutions











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.






download


Recommended Reading

Enabling xp_cmdshell in SQL Server

Encrypting passwords for use with Python and SQL Server

Understanding SQL Server fixed database roles

How to configure SSL encryption in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role





get free sql tips
agree to terms


Learn more about SQL Server tools