SQL Server中查詢使用者的物件許可權和角色的方法

-- 查詢使用者的object許可權

exec sp_helprotect NULL, 'UserName'

-- 查詢使用者擁有的role

exec sp_helpuser 'UserName'

-- 查詢哪些使用者擁有指定的系統role

exec sp_helpsrvrolemember 'sysadmin'

-- 可查詢巢狀role

WITH tree_roles as

(

SELECT role_principal_id, member_principal_id

FROM sys.database_role_members

WHERE member_principal_id = USER_ID('UserName')

UNION ALL

SELECT c.role_principal_id,c.member_principal_id

FROM sys.database_role_members as c

inner join tree_roles

on tree_roles.member_principal_id = c.role_principal_id

)

SELECT distinct USER_NAME(role_principal_id) RoleName

FROM tree_roles

-- 其他許可權相關基本表

select * from sysusers

select * from syspermissions

-- Who has access to my SQL Server instance?

SELECT

name as UserName, type_desc as UserType, is_disabled as IsDisabled

FROM sys.server_principals

where type_desc in('WINDOWS_LOGIN', 'SQL_LOGIN')

order by UserType, name, IsDisabled

-- Who has access to my Databases?

SELECT

dp.name as UserName, dp.type_desc as UserType, sp.name as LoginName, sp.type_desc as LoginType

FROM sys.database_principals dp

JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id

order by UserType

select * from sys.database_principals

-- Server Roles

select

p.name as UserName, p.type_desc as UserType, pp.name as ServerRoleName, pp.type_desc as ServerRoleType

from sys.server_role_members roles

join sys.server_principals p on roles.member_principal_id = p.principal_id

join sys.server_principals pp on roles.role_principal_id = pp.principal_id

where pp.name in('sysadmin')

order by ServerRoleName, UserName

-- Database Roles

SELECT

p.name as UserName, p.type_desc as UserType, pp.name as DBRoleName, pp.type_desc as DBRoleType, pp.is_fixed_role as IfFixedRole

FROM sys.database_role_members roles

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

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

where pp.name in('db_owner', 'db_datawriter')

-- What can these users do?

SELECT

grantor.name as GrantorName, dp.state_desc as StateDesc, dp.class_desc as ClassDesc, dp.permission_name as PermissionName ,

OBJECT_NAME(major_id) as ObjectName, GranteeName = grantee.name

FROM sys.database_permissions dp

JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id

JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

where permission_name like '%UPDATE%'