Find database principal and its member in SQL Server 2005

Sunday, May 3, 2009 |

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

MemPri.Name as MemberPrincipal,     
RolePri.Name as RolePrincipal,
MemPri.Create_Date as DateCreated,
MemPri.Modify_date as DateModified,
MemPri.Type_Desc as Description
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
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of