sqlserver的权限查询
--有关数据库的权限查询:
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;
授予单个表、视图、存储过程,就比较简单了!
网页题目:sqlserver的权限查询
本文路径:http://scyanting.com/article/ghhjch.html