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;

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