sql server的权限查询

--有关数据库的权限查询：
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

具体的一些查询
--查看谁可以访问实例
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

--查看谁可以访问数据库
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

--角色权限查询
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

--数据库角色
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')

--查看用户的权限
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%'

--其它说明
通过存储过程来查询
EXEC sp_helprotect NULL, NULL ,null,'golden_ro';

参数1：Owner sysname Name of the object owner
参数2：Object sysname Name of the object.
参数3：Grantee sysname Name of the principal to which permissions were granted
参数4：Grantor sysname Name of the principal that granted permissions to the specified grantee.
暂时列常用的四个参数！用法如上！
如查询表的授权情况

EXEC sp_helpuser;
参数1：UserName sysname Users in the current database.
参数2：RoleName sysname Roles to which UserName belongs.
参数3：LoginName sysname Login of UserName.
参数4：DefDBName sysname Default database of UserName.

最近在跟用户授权的时候，发现要授予全部存储过程的执行权限，搞了很久，终于搞懂
grant execute to username;

授予单个表、视图、存储过程，就比较简单了！