SQL Server Role Membership

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: April 5, 2011

How well do you know the security in your SQL instances?  Do you know who has sysadmin level permissions?  SQL Server provides a few methods for you to find out who is a member of which roles at the server level.

For those that like to point and click, you can always navigate through the GUI (SSMS) to determine which users or groups have been granted access to the sysadmin fixed server role.  For those that want something a bit faster, you can use a script to return this information for you.  Just as with most things TSQL, there are numerous different ways of writing this script.  Here are some of those methods.

Verifying Server Role membership

[codesyntax lang="tsql"]

[/codesyntax]

With this script, I am querying the sys.server_role_members and sys.server_principals views.  For simplicity sake, I am also using the SUSER_NAME() function to derive the role name.  Note that I threw in a union all to get back the ‘Public’ group membership.  The public group is a special group that does not appear when querying the sys.server_role_members view – but everybody is a member.

An Alternative

[codesyntax lang="tsql"]

[/codesyntax]

This one is quite simple as well.  Note that I am not employing the use of the SUSER_NAME function but have used another join in its place.  I am also only interested in adding the public role at this time to SQL Users, Windows Users and groups that are not disabled.  That information in the where clause is optional and is present to demonstrate the ability to quickly pare down the results.

Another Option

This is really the easiest of the three queries.

[codesyntax lang="tsql"]

[/codesyntax]

I am still employing the union statement to populate the public role.  Notice the difference in the first half of the query though.  I am simply using the SUSER_NAME function for both principal_ids being retrieved from the server_role_members view.  This is a little easier to follow and write.  Performance considerations put this last query as the most efficient on my systems with the first query shared being a close second.

All of these will return your group memberships quickly and in a manner that is quickly understandable (names instead of numbers).  The use of a query such as these would be a stepping stone into auditing the permissions that are in place on your server.  It is also great to quickly validate who has sysadmin access and to use that to confirm that the account should have sysadmin access.

1 Comment - Leave a comment
  1. [...] to rewrite it as a part of my entry for this months blog party.  You can read up on that script here.  My dissatisfaction with the query was that it felt klugy.  I wanted to write the query with [...]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
April 2011
M T W T F S S
« Mar   May »
 123
45678910
11121314151617
18192021222324
252627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, October 21, 2014