SQL Server中查询用户的对象权限和角色的方法

 

--SQL Server中查询用户的对象权限和角色的方法

 -- 查询用户的object权限

exec sp_helprotect NULL, 'sa'

-- 查询用户拥有的role

exec sp_helpuser 'public'

-- 查询哪些用户拥有指定的系统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%'