查询数据库账号的所有权限

SQLServer走起 2020-11-11

3929

在SQL Server数据库中，登录账号分类如下：

(1) SQL账号，需要单独设置密码，比如：sa；

(2) Windows账号，登录windows的账号，比如: administrator，不需要另设密码；

(3) Windows Group 账号, 为本地用户组或域用户组，将组添加到登录，组成员不需要单独创建登录；

查看Windows账号，是否属于某一个/多个用户组：

exec xp_logininfo 'windows_acount','ALL'  
--域用户格式为：domain_name\account_name

以下脚本，均假设最终登录账号为：test_login，所有数据库对应的user为test_user

一. 有没有权限

1. 检查有没有登录权限

--是否存在有效的登录账号：是否被禁用，sql login还有：密码是否过期，是否被锁定
select is_disabled, loginproperty(name,'Isexpired') is_expired, loginproperty(name,'Islocked') is_locked, * from sys.server_principals
where name = 'test_login'

2. 检查有没有访问某数据库的权限


USE DBA
GO
--检查是否有数据库的CONNECT权限即可
select b.* from sys.database_principals ainner join sys.database_permissions b on a.principal_id = b.grantee_principal_id 
where SUSER_SNAME(a.sid) = 'test_login'and b.permission_name = 'CONNECT'--老的系统表sysusers也可以检查
SELECT name, hasdbaccess,* FROM sysusers a
WHERE SUSER_SNAME(a.sid) = 'test_login'

如果有很多个数据库，写个游标1个个去检查即可。

3. 检查有没有某个对象的权限

检查有没有某个对象的权限，一般是去尝试运行下脚本比较直观，如果去查各种权限表，角色错综复杂时，很难分辨；

SQL Server 2008之后引入了HAS_PERMS_BY_NAME这个函数，它可以检查当前账号的各种权限，检查其他用户需要用EXECUTE AS来切换：


USE DBA 
GO
EXECUTE AS user = 'test_user'GO--对象权限
SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT');
SELECT HAS_PERMS_BY_NAME('sp_send_dbmail', 'OBJECT', 'EXEC');
--架构权限
SELECT HAS_PERMS_BY_NAME('test_schema', 'SCHEMA', 'SELECT');
REVERT;
GO

对于是否有登录、访问数据库的权限，用这个函数也可以判断：


USE master
GO
EXECUTE AS login = 'test_login'GO--登录权限，本机前2个参数为空即可SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'CONNECT SQL');
REVERT;
GO
USE DBA
GO
EXECUTE AS user = 'test_user'GO--数据库权限SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CONNECT');
REVERT;

二. 有哪些权限

权限可以直接分配给账号，也可以分配给账号所属的role，所以要把账号自身权限、所属role权限合并才是最终的账号权限。

Windows账号权限还可以通过用户组分配，所以还要检查这个Windows账号有没有属于某个用户组，如果有还需要加上这个用户组的权限；

下面的脚本，仅检查单个用户/用户组权限。

1. 实例级的权限



use master
GO
declare @svr_principal_name varchar(1024)set @svr_principal_name = 'test_login'declare @svr_principal_id intselect @svr_principal_id = principal_id 
from sys.server_principals pwhere p.name = @svr_principal_nameif OBJECT_ID('tempdb..#tmp_svr_role','U') is not null
    drop table #tmp_svr_role;create table #tmp_svr_role
(
member_principal_id     int,
member_principal_name   varchar(512),
role_principal_id       int, 
role_principal_name     varchar(512)
)--获取登录账号的所有server role, 从sql server 2012开始，server role可以自定义，成员仅可为fixed server role;with tmpas(select * from sys.server_role_members 
where member_principal_id = @svr_principal_idunion allselect rm.* from sys.server_role_members rminner join tmp 
on rm.member_principal_id = tmp.role_principal_id
)insert into #tmp_svr_role 
select a.member_principal_id, b.name,
       a.role_principal_id, c.name from tmp ainner join sys.server_principals bon a.member_principal_id = b.principal_idinner join sys.server_principals con a.role_principal_id = c.principal_id--登录账号自身权限, sys.server_permissions不包含fixed server role权限，同时手动排除掉public权限select a.principal_id as member_principal_id, a.name as member_principal_name,       null as role_principal_id, null as role_principal_name,
       b.permission_name, b.state_descfrom sys.server_principals ainner join sys.server_permissions bon a.principal_id = b.grantee_principal_idwhere a.principal_id = @svr_principal_idand b.permission_name <> 'CONNECT SQL'union all--server role权限，包含fixed server role和自定义的server roleselect a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,       isnull(b.permission_name,'Fixed Server-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_descfrom #tmp_svr_role aleft join sys.database_permissions bon a.role_principal_id = b.grantee_principal_idunion all--public server role权限，不可以取消public权限，它是每个登录账号的最小权限，仅可连接数据库实例select @svr_principal_id as member_principal_id,@svr_principal_name as member_principal_name,
       principal_id as role_principal_id, name as role_principal_name, 
       'CONNECT SQL' as permission_name, 'GRANT' as state_descfrom sys.server_principalswhere name = 'public'

注意：服务器角色的权限可以做什么具体的事情，exec sp_srvrolepermission 有大致的介绍，但是也并没有全部列出每一种数据库操作，因为有些操作是被更高级的操作包含的。

2. 数据库级的权限

仅列出数据库级别的权限，具体的对象名称并没有列出。



use DBA
GO
declare @svr_principal_name varchar(1024)set @svr_principal_name = 'test_login'declare @db_principal_id    int,        @db_principal_name  varchar(512)select @db_principal_id = principal_id,       @db_principal_name = namefrom sys.database_principals pwhere SUSER_SNAME(sid) = @svr_principal_nameif OBJECT_ID('tempdb..#tmp_db_role','U') is not null
    drop table #tmp_db_role;create table #tmp_db_role
(
member_principal_id     int,
member_principal_name   varchar(512),
role_principal_id       int, 
role_principal_name     varchar(512)
)--获取登录账号在当前数据库的所有database role;with tmpas(select * from sys.database_role_members 
where member_principal_id = @db_principal_idunion allselect rm.* from sys.database_role_members rminner join tmp 
on rm.member_principal_id = tmp.role_principal_id
)insert into #tmp_db_role 
select a.member_principal_id, b.name,
       a.role_principal_id, c.name from tmp ainner join sys.database_principals bon a.member_principal_id = b.principal_idinner join sys.database_principals con a.role_principal_id = c.principal_id--登录账号在当前数据库的自身权限, sys.database_permissions不包含fixed database role权限，同时手动排除掉public权限select a.principal_id as member_principal_id, a.name as member_principal_name,       null as role_principal_id, null as role_principal_name,
       b.permission_name, b.state_descfrom sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idwhere a.principal_id = @db_principal_idand b.permission_name <> 'CONNECT'union all--database role权限，包含fixed database role和自定义的database roleselect a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,       isnull(b.permission_name,'Fixed Database-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_descfrom #tmp_db_role aleft join sys.database_permissions bon a.role_principal_id = b.grantee_principal_idunion all--public database role权限，不可以取消public权限，它是每个登录账号映射到当前数据库的最小权限，仅可连接当前数据库select @db_principal_id as member_principal_id, @db_principal_name as member_principal_name,
       principal_id as role_principal_id, name as role_principal_name, 
       'CONNECT' as permission_name, 'GRANT' as state_descfrom sys.database_principalswhere name = 'public'

注意：sysadmin的账号在数据库里可能并没有做映射，但权限是有的，隐式映射的用户是dbo

3. 对象级的权限

sys.database_permissions有很多对象类型，major_id, minor_id取决于class_desc，不同的对象关联不同的系统表/视图，脚本里仅列出了最常见的OBJECT_OR_COLUMN, SCHEMA对象权限。



--建立测试用的架构，对象，列use DBAGOif object_id('test_grant','U') is not null
    drop table test_grantGOcreate table test_grant(c1 int, c2 int, c3 int)grant select (c1, c2) on test_grant to test_user;if object_id('test_schema.test_t1','U') is not null
    drop table test_schema.test_t1GOif exists(select 1 from sys.schemas where name  = 'test_schema')    drop schema test_schemaGOcreate schema test_schemacreate table test_schema.test_t1(c1 int, c2 int)grant select on schema::test_schema to test_user;GO--开始获取对象权限use DBAGOdeclare @svr_principal_name varchar(1024)set @svr_principal_name = 'test_login'declare @db_principal_id    int,        @db_principal_name  varchar(512)select @db_principal_id = principal_id,       @db_principal_name = namefrom sys.database_principals pwhere SUSER_SNAME(sid) = @svr_principal_nameif OBJECT_ID('tempdb..#tmp_db_role','U') is not null
    drop table #tmp_db_role;create table #tmp_db_role
(
member_principal_id     int,
member_principal_name   varchar(512),
role_principal_id       int, 
role_principal_name     varchar(512)
)--获取登录账号在当前数据库的所有database role;with tmpas(select * from sys.database_role_members 
where member_principal_id = @db_principal_idunion allselect rm.* from sys.database_role_members rminner join tmp 
on rm.member_principal_id = tmp.role_principal_id
)insert into #tmp_db_role 
select a.member_principal_id, b.name,
       a.role_principal_id, c.name from tmp ainner join sys.database_principals bon a.member_principal_id = b.principal_idinner join sys.database_principals con a.role_principal_id = c.principal_id--登录账号在当前数据库的自身对象权限(OBJECT_OR_COLUMN)select a.principal_id as member_principal_id, a.name as member_principal_name,       null as role_principal_id, null as role_principal_name,
       o.name as major_name, c.name as minor_name,
       b.permission_name, b.state_descfrom sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idleft join sys.objects oon b.major_id = o.object_idleft join sys.columns c 
on (b.major_id = c.object_id and b.minor_id = c.column_id)where a.principal_id = @db_principal_idand b.class_desc = 'OBJECT_OR_COLUMN'union all--登录账号在当前数据库的自身对象权限(SCHEMA)select a.principal_id as member_principal_id, a.name as member_principal_name,       null as role_principal_id, null as role_principal_name,
       s.name as major_name, null as minor_name,
       b.permission_name, b.state_descfrom sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idleft join sys.schemas son b.major_id = s.schema_idwhere a.principal_id = @db_principal_idand b.class_desc = 'SCHEMA'union all--database role的对象权限(OBJECT_OR_COLUMN)select a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,
       o.name as major_name, c.name as minor_name,
       b.permission_name, b.state_descfrom #tmp_db_role ainner join sys.database_permissions b --inner join, 仅自定义的database roleon a.role_principal_id = b.grantee_principal_idleft join sys.objects oon b.major_id = o.object_idleft join sys.columns c 
on (b.major_id = c.object_id and b.minor_id = c.column_id)where b.class_desc = 'OBJECT_OR_COLUMN'union all--database role的对象权限(SCHEMA)select a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,
       s.name as major_name, null as minor_name,
       b.permission_name, b.state_descfrom #tmp_db_role ainner join sys.database_permissions b --inner join, 仅自定义的database roleon a.role_principal_id = b.grantee_principal_idleft join sys.schemas son b.major_id = s.schema_idwhere b.class_desc = 'SCHEMA'/*union all
--public role有一些系统视图的select权限，可以忽略
select a.principal_id as member_principal_id, a.name as member_principal_name,
       null as role_principal_id, null as role_principal_name,
       o.name as major_name, c.name as minor_name,
       b.permission_name, b.state_desc
from sys.database_principals a
inner join sys.database_permissions b
on a.principal_id = b.grantee_principal_id
left join sys.all_objects o
on b.major_id = o.object_id
left join sys.all_columns c 
on (b.major_id = c.object_id and b.minor_id = c.column_id)
where a.name = 'public'*/

注意：如果对象的权限是通过role衍生的，而不是直接分配给user或者role，那么并不会被列出来。试想sysadmin 的角色，难道要列出所有数据库的所有对象吗？

三. 查看自己的权限

1. 有没有登录权限

登录失败并不一定是没权限，还是找别人来检查自己账号的登录权限吧；

2. 有没有数据库访问权限

--列出所有可访问的数据库
SELECT *FROM sys.databases WHERE HAS_DBACCESS(name) = 1

3. 有没有对象访问权限

用上面提到HAS_PERMS_BY_NAME函数，它可以检查当前账号的各种权限；

SELECT HAS_PERMS_BY_NAME('test_sp', 'Object' , 'Execute')
SELECT HAS_PERMS_BY_NAME('test', 'Database' , 'Execute')

4. 有哪些权限


--实例级权限SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 
--数据库级权限SELECT * FROM fn_my_permissions ('DBA', 'DATABASE'); 
--对象权限，只能一个个对象检查，不能一次返回所有对象权限，和HAS_PERMS_BY_NAME类似SELECT * FROM fn_my_permissions ('test_grant', 'OBJECT');

用于检查自己权限的方法，同样也可以检查其他账号，用EXECUTE AS切换账号即可。

文章转载自：

https://www.cnblogs.com/seusoftware/p/4848940.html

文章经作者授权转载，版权归原文作者所有

图片来源于网络，侵权必删！

数据库

文章转载自SQLServer走起，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

查询数据库账号的所有权限

评论