SQL: Get Login Database and Server Role Membership


When trying to query information about server and database role membership, SQL Server ships with some helpful stored procedures like sp_helpsrvrolemember and sp_helprolemember. These are helpful when you are looking for the members of a given role, but not as helpful when you are looking for the reverse; the roles for which a given database user is a member.

After some searching, I found the sp_helpuser stored procedure which will give you a bunch of information about a database user including its group membership. But, I had two problems with this, it was a stored procedure so I could not preform any operations on the results without inserting them into a temporary table and it didn’t provide me with any information about the server roles of which the associated login was a member. Unsatisfied with the stock stored procedures provided I turned to the system views. Using the sys.server_role_members and sys.database_role_members I was able to get the needed information.

Both of the mentioned views return two columns, one for the role principal id and one for the member principal id. Thus, to get a list of server and database roles for which a given login or database user is a member, all we need to do is select the rows associated with the login or user.

Server Role Membership

DECLARE @login sysname
SET @login= 'nick'

SELECT SUSER_NAME(SRoleMembers.role_principal_id) AS [Role]
FROM sys.server_role_members as SRoleMembers
WHERE SUSER_NAME(SRoleMembers.member_principal_id) = @login

Database Role Membership

DECLARE @username sysname
SET @username = 'nick';

-- USE a Common Table Expression to build a recursive list
-- of roles for which this user is a member
WITH UserDBRoles (role_principal_id)
AS
(
	SELECT DBRoleMembers.role_principal_id 
	FROM sys.database_role_members AS DBRoleMembers
	WHERE USER_NAME(DBRoleMembers.member_principal_id) = @username
  UNION ALL
	SELECT DBRoleMembers.role_principal_id
	FROM sys.database_role_members AS DBRoleMembers
	INNER JOIN UserDBRoles ON DBRoleMembers.member_principal_id = UserDBRoles.role_principal_id
)

SELECT USER_NAME(role_principal_id) [Role]
FROM UserDBRoles

Both of these use the SUSER_NAME and USER_NAME functions which convert a principal id to its associated login, user, or role where SUSER_NAME is used for server level principals and USER_NAME is used for database level principals. This conversion could have also been doing with a join using the sys.server_principals and sys.database_principals views but I figured this syntax was a bit easier to read.

Retrieving the roles for the database user is a bit more tricky as you can have nested roles. In this example, the database user nick is a member of the Manager role and in turn the Manager role is a member of the Leasing role. If we were to follow the same logic as the statement for the server roles, the Leasing role would not be returned in our list. To solve this we can use a Common Table Expression to create a recursively defined table that only will exists for the duration of the current query.

Advertisements
Posted in SQL. Tags: . Leave a Comment »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: