Ads 720 x 90

System views – login/permissions

---logins:

SELECT
*
FROM
sys.server_principals


 

--database users (run in the db)

SELECT
*
FROM
sys.database_principals


 

--db roles


 

SELECT dp.type_desc, dp.name, p.name

FROM
sys.data base_role_members r

JOIN
sys.database_principals dp ON r.role_principal_id = dp.principal_id

JOIN
sys.database_principals p ON r.member_principal_id = p.principal_id


 

--server roles:

SELECT dp.type_desc, dp.name, p.name

FROM
sys.server_role_members r

JOIN
sys.server_principals dp ON r.role_principal_id = dp.principal_id

JOIN
sys.server_principals p ON r.member_principal_id = p.principal_id


 

--db perms:

SELECT class_desc, permission_name, p.name


FROM
sys.database_permissions dp

JOIN
sys.database_principals p ON dp.grantee_principal_id = p.principal_id


 

--server perms:

SELECT class_desc, permission_name, p.name


FROM
sys.server_permissions dp

JOIN
sys.server_principals p ON dp.grantee_principal_id = p.principal_id

Related Posts

There is no other posts in this category.

Total Pageviews

Subscribe Our Newsletter