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.