Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Server Role Membership Validation

By Shawn Melton,

Introduction

Whether for troubleshooting purposes, auditing, or as a step during deployments, there are times you will need to verify a login’s membership for a given server role or for all server roles on a SQL Server instance. This article will review the basic steps and options available.

SSMS - Login properties window

If I am in troubleshooting mode there are times it is quicker to go through a few clicks, than for me to type out certain commands, just depends. We will first start out with the login properties window in SQL Server Management Studio (SSMS). Within SSMS you can drill down to the Logins folder (Security\Logins) in Object Explorer and see the list of all the current logins. Viewing the login properties window you can go to the "Server Roles" page and view all the server-level roles the login is currently a member of, this will be denoted with a check mark beside the associated role.

In the following example I am locating my login, "ORKO\Shawn", within Object Explorer to verify what server roles I am a member:

Left-click on the given login twice to view the properties window for that login. As indicated by the check marks my account is a member of the public and sysadmin roles. To note, by default all logins are a member of the public role.

T-SQL - Functions

Programatically speaking, you may need to verify membership of a login at the server level before performing some action or execution code. You can use the security function IS_SRVROLEMEMBER for just this occasion. This function has actually been around since SQL Server 2000 and can be very useful for quick checks. Going on the same example as above if I want to verify my login is a member of the sysadmin role the following line of code will do this:

SELECT IS_SRVROLEMEMBER('sysadmin','ORKO\Shawn') AS isMember

There are three possible outputs from using this function:

  1. 0 = login is not a member of the specified server role
  2. 1 = login is a member of the specified server role
  3. NULL = Either the role or login is not valid, or you do not have permissions to view role membership

The output from this command is illustrated in the image below:

T-SQL - Security Catalog Views

If you have a need to programmatically find all server roles a login is a member of you can utilize the security catalog views. There is only one security catalog view that can be used to view whether a specified login is a member of a server role, and that is sys.server_role_members. As of SQL Server 2012 you can still access the backward compatiblity view, sys.syslogins to see server role membership, but this is not guaranteed to exist in future versions so it is best to not use it anymore.

Now, the sys.server_role_members view only contains two columns:

  1. role_principal_id
  2. member_principal_id

The principal ID is a unique value for both a login and a server-role and each ID will exist as a record within the sys.server_principals catalog view. You can create a query that would contain two joins, one on the role_principal_id and then another on the member_principal_id. You can see this type of query in the example provided within the Books Online article of the view itself.

A method that is a bit easier to remember off the top of your head is the built-in function, SUSER_NAME(). You pass the principal ID to this function and it will return the name of that principal as an nvarchar(128) value. You can also use this function within the WHERE clause of your query. So the code below will return all the roles the "Test1" login is a member of:

SELECT SUSER_NAME(role_principal_id) AS ServerRole
 FROM sys.server_role_members
 WHERE SUSER_NAME(member_principal_id) = 'Test1'

The output of this query is shown below:

Summary

In review, this article showed the basic steps to use in viewing the server-role membership of a given login through SSMS and programatically using the T-SQL security function and catalog views available in SQL Server. I will note that PowerShell could also be used to perform these validations, however for the purpose of this article I stuck with the more common methods used.

Total article views: 2271 | Views in the last 30 days: 20
 
Related Articles
ARTICLE

Query accounts, domain groups, and members who have admin membership.

This script leverages master.sys.server_principals and xp_logininfo to return accounts, domain group...

BLOG

Mapping Database Principals to Server Principals

A question on the forum asked how to find all the database mappings for a particular login. If you'r...

ARTICLE

Stairway to SQL Server Security Level 3: Principals and Securables

What is a SQL Server principal? And what does it get a permission on? In this stairway level, you’ll...

FORUM

The server principal 'MyDomain\DomainUserA' already exists.

The server principal 'MyDomain\DomainUserA' already exists.

BLOG

MDX Member Functions

There are a large number of Member functions in MDX that do everything from returning ancestry of a ...

Tags
security    
server roles    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones