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;

授予單個表、檢視、儲存過程，就比較簡單了！