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.