SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

Today I was digging about principal and member of that principal from sys.database_principal. It might be handy and very useful sometime to list out all users’ list with its associated principal name. I have used sys.database_pricipal to get details about principal and its member’s details and sys.database_role_members for relationship of principal and its member.

Have a look at my T-SQL

SELECT

MemPri.Name as MemberPrincipal,     

RolePri.Name as RolePrincipal,

MemPri.Create_Date as DateCreated,

MemPri.Modify_date as DateModified,

MemPri.Type_Desc as Description

FROM

sys.database_role_members RoleMem

JOIN sys.database_principals RolePri ON RoleMem.role_principal_id = RolePri.principal_id

JOIN sys.database_principals MemPri ON RoleMem.member_principal_id = MemPri.principal_id

order by MemberPrincipal

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com