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
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
0 comments:
Post a Comment