Search This Blog

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

No comments: