SQL Server 安全篇——安全元数据（1）——安全主体（Principal）元数据

本文属于SQL Server安全专题系列

 虽然大量的安全元数据（security metadata）可以从SSMS中查询（指鼠标操作），但是有些元数据仅能通过T-SQL来查看。完整的安全元数据足以单独成书，这里仅介绍一些有用的或者可能会用到的内容。

安全主体元数据：

 当在实例上实施安全策略时，很有可能就要收集很多安全实体或者安全对象的信息。比如一个策略是所有数据库必须属于sa。那么为了验证这个情况，必须找到每个库的当前拥有者，然后进行对应修改。如果一个实例有200个库，那么GUI操作恐怕会很惨。

 对于使用GUI，更应该借用元数据来实现，每个数据库的拥有者可以通过sp_MShasdbaccess存储过程或者从sys.databases目录视图来获取。

 sp_MShasdbaccess存储过程不接受参数，直接返回库名及其拥有者，如下图，但是注意这个存储过程只返回有权限访问的库的信息：

 如果需要更详细的信息，可以从sys.databases中获得。如果只是上面的需求，可以从该视图中获取sid（安全ID），然后使用SUSER_SNAME()系统函数翻译成名字：

 对于SUSER_SNAME()和SUSER_NAME()，两者都可以返回登录名，但是前者参数为SID而后者是一个登录ID（安全主体ID）。

查找用户实际权限：

 当服务器角色和数据库角色的曾经比较复杂的时候，如果权限也直接授权到用户，那么权限识别工作将非常痛苦，而此时可以使用sys.fn_my_permissions()系统函数来协助，其参数如下表：

参数	描述
securable	用户权限所属的安全对象名字
securable_class	需要查找的安全对象类型，如SERVER、DATABASE或对象

 函数返回的结果描述如下表：

列	描述
entity_name	安全对象的名字
subentity_name	如果安全对象有列，那么这列就包含列名，否则为NULL
permission_name	由安全主体分配的权限名。

 这个函数用于返回关于调用函数的用户信息，但是也可以通过EXECUTE AS 来指定检查的用户信息，如下面脚本：

USE master 
GO 
--创建一个演示登录
CREATE LOGIN DemoLogin WITH PASSWORD=N'Pa$$w0rd', CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 
--添加到sysadmin角色
ALTER SERVER ROLE sysadmin ADD MEMBER DemoLogin 
GO
USE AdventureWorks2016
GO 
--返回登录名
SELECT SUSER_SNAME() ; 
EXECUTE AS LOGIN = 'DemoLogin' ; 
SELECT SUSER_SNAME() ; 
REVERT ; 
SELECT SUSER_SNAME() ; 

 本机结果如下：

 然后在演示使用sys.fn_my_permissions函数组合EXECUTE AS子句来查找用户权限。

EXECUTE AS LOGIN = 'DemoLogin' 
   SELECT o.name 
        , a.entity_name 
        , a.subentity_name 
        , a.permission_name 
   FROM sys.objects o 
   CROSS APPLY sys.fn_my_permissions(CONCAT( 
                                        QUOTENAME( 
                                                SCHEMA_NAME(schema_id)) 
                                     , '.' 
                                     , QUOTENAME(o.name)) 
                                     , 'OBJECT') a   
   UNION ALL 
   SELECT d.name 
        , a.entity_name 
        , a.subentity_name 
        , a.permission_name 
   FROM sys.databases d 
   CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a 
   UNION ALL 
   SELECT @@SERVERNAME COLLATE Latin1_General_CI_AS 
          , a.entity_name 
          , a.subentity_name 
          , a.permission_name 
   FROM fn_my_permissions(NULL, 'SERVER') a 
   ORDER BY 1 
REVERT 

 结果太多，这里只能截取一小部分，读者可以自行测试：

 结果包含了实例、库、对象（当前库，包含索引等）层级的实际权限。语句包含三个独立部分并用UNION 连接起来。第一部分从sys.objects中返回对象名及架构名，传入sys.fn_my_permissions()函数中。第二部分把sys.objects的行为变成sys.databases，及把对象上升到库层面。最后一部分是实例层面的权限信息。

 如有相关脚本会尽量同步更新到本文。