Tag Archives: sys.database_role_members

Find database principal and its member in SQL Server 2005

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah