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 www.jb200.com

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%'