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