前面博文《SQL Server服务器级权限》介绍了服务器级安全主题的权限，本文将继其之后，来介绍一下数据库级安全主题具有的权限，以及如何赋予、拒绝数据库用户、角色权限，并介绍查看用户、角色具有哪些权限的方法。
本文同样使用 sys.fn_builtin_permissions 来查看安全主题数据库具有的权限
SELECT * FROM sys.fn_builtin_permissions('database')SQL Server 2008拥有的数据库权限：
- CREATE TABLE：授予或拒绝创建表的权限。此权限隐式授予db_ddladmin和db_owner固定数据库角色。在SQL Server 2005或更高兼容性模式中，用户仍然需要ALTER SCHEMA 权限才能在特定架构中创建表。
- CREATE VIEW：赋予或拒绝创建视图的权限。在SQL Server 2005或更高兼容性模式中，用户仍然需要ALTER SCHEMA 权限才能在特定架构中创建视图。
- CREATE PROCEDURE：赋予或拒绝创建存储过程的权限。数据库固定角色db_ddladmin和db_owner隐含此权限。在SQL Server 2005或更高版本，用户还需要ALTER SCHEMA权限才能在特定对应架构下创建它。
- CREATE FUNCTION：赋予或拒绝创建函数的权限。数据库固定角色db_ddladmin和db_owner隐含此权限。在数据库2005或更高版本中，用户还需要ALTER SCHEMA权限才能在特定架构下创建它。
- CREATE RULE：赋予或拒绝创建规则的权限。数据库固定角色db_ddladmin和db_owner隐含这个权限。在SQL Server 2005 或者更高版本中，用户还需要ALTER SCHEMA 权限才能在这个架构下创建它。
- CREATE DEFAULT：赋予或拒绝创建默认值的权限。这数据库固定角色db_ddladmin和db_owner隐含权限。在SQL Server 2005或更高兼容版本中，用户还需要ALTER SCHEMA 权限在特定架构中创建它。
- BACKUP DATABASE：赋予或者拒绝备份数据库的权限，db_backupoperator固定数据库角色有此权限
- BACKUP LOG：赋予或者拒绝备份数据库日志的权限，db_backupoperator固定数据库角色有此权限
- CREATE DATABASE：赋予或拒绝master数据库下的用户CREATE或DROP数据库的权限
- CREATE TYPE：赋予或者拒绝创建类型的权限。数据库固定角色db_ddladmin和db_owner隐含此权限。在SQL Server 2005或者更高版本中，用户还需要ALTER SCHEMA 权限才能在对应架构中创建它。
- CREATE ASSEMBLY：赋予或拒绝某数据库中CREATE或DROP一个程序集的权限。如果程序集权限需要EXTERNAL_ACCESS，登陆账户必须同时拥有EXTERNAL ACCESS ASSEMBLY权限。如果权限设置为UNSAFE，登陆账户必须为sysadmin固定服务器角色。和ALTER ANY ASSEMBLY 不同，用户必须拥有该程序集，或者有对程序集有CONTROL权限，才能删除它。
- CREATE XML SCHEMA COLLECTION：赋予或拒绝创建XML架构集合的权限。数据库固定角色db_ddladmin和db_owner隐含此权限。在数据库2005或更高版本中，用户还需要ALTER SCHEMA权限才能在特定架构下创建它。
- CREATE SCHEMA：赋予或拒绝在数据库中创建架构的权限。和ALTER ANY SCHEMA 不同，用户只能删除其拥有的架构，或删除对其有CONTROL权限的架构。
- ALTER ANY SCHEMA：赋予或拒绝在数据库CREATE、DROP架构的权限，执行转移架构中对象（ALTER SCHEMA Schema_Name TRANSFER [ OBJECT| Type | XML Schema Collection:: ] securable_name）时，用户还需要securable_name的CONTROL权限
- CREATE SYNONYM：赋予或拒绝创建同义词的权限。固定数据库角色db_ddladmin和db_owner隐含此权限。在SQL Server 2005 或者更高版本中，用户还需要ALTER SCHEMA权限才能在对于架构中创建它。
- CREATE AGGREGATE：赋予或拒绝创建用户定义的来自程序集的聚合函数，必须同时拥有对程序集的引用（REFERENCES）权限。
- CREATE ROLE：赋予或拒绝CREATE、ALTER、DROP用户定义的数据库角色权限。和ALTER ANY ROLE 不同，用户必须拥有这个角色或者对这个角色有CONTROL权限才能删除它。
- ALTER ANY ROLE：赋予或拒绝CREATE、ALTER、DROP用户定义的数据库角色权限。
- CREATE QUEUE：赋予或拒绝CREATE、ALTER、DROP代理服务队列的权限，用户必须是队列的拥有者才能删除它。
- CONNECT：赋予或拒绝连接数据库的权限，当创建新用户时，该权限默认被赋予它。
- CONNECT REPLICATION：授予或拒绝了作为订阅服务器连接到数据库以通过复制检索发布的能力。
- CHECKPOINT：赋予或拒绝对数据库发起CHECKPOINT语句的权限，db_backupoperator固定数据库角色有此权限
- SUBSCRIBE QUERY NOTIFICATIONS：赋予或拒绝当创建订阅特定查询结果将发生改变时查询通知的权限
- AUTHENTICATE：赋予或拒绝跨数据库模拟的能力，通常显示的访问是不允许的
- SHOWPLAN：赋予或拒绝数据库中查询的执行计划的权限。
- ALTER ANY USER：赋予或拒绝数据库内CREATE、DROP或ALTER用户的权限，db_accessadmin固定数据库角色有此权限
- ALTER ANY APPLICATION ROLE：赋予或拒绝CREATE、DROP或ALTER应用角色。db_securityadmin固定数据库角色隐含此权限。
- ALTER ANY ASSEMBLY：赋予或拒绝CREATE、DROP或ALTER CLR程序集，db_ddladmin固定数据库角色有此权限
- ALTER ANY DATASPACE：赋予或拒绝创建数据库分区的权限。db_ddladmin固定数据库角色有此权限
- CREATE MESSAGE TYPE：赋予或拒绝创建代理服务信息类型的权限，和ALTER ANY MESSAGE TYPE不同，用户必须是信息类型的拥有者才能删除它。
- ALTER ANY MESSAGE TYPE：赋予或拒绝创建代理服务的信息类型权限，当CREATE或ALTER 信息类型时指定架构集合时，用户必须有对指定架构结合的引用（REFERENCES）权限
- CREATE CONTRACT：赋予或拒绝创建（CREATE）服务代理协议的权限。和ALTER ANY CONTRACT不同，用户必须是协议的拥有者或者对协议拥有CONTROL权限的用户才能删除它。
- ALTER ANY CONTRACT：赋予或拒绝CREATE和DROP 服务代理的协议的权限，db_ddladmin固定数据库角色有此权限
- CREATE SERVICE：赋予或拒绝CREATE或DROP代理服务的服务权限。创建服务的用户必须同时拥有服务的所有队列和协议的REFERENCES权限。和ALTER ANY SERVICE不同，用户必须是服务的拥有者才能删除它。
- ALTER ANY SERVICE：赋予或拒绝CREATE、ALTER或DROP代理服务的服务权限。创建服务的用户必须同时拥有服务的所有队列和协议的REFERENCES权限。
- CREATE REMOTE SERVICE BINDING：赋予或拒绝CREATE、ALTER、DROP代理服务的远程服务绑定权限，和ALTER ANY REMOTE SERVICE BINDING 不同，用户必须是远程服务绑定的拥有者才能删除它。
- ALTER ANY REMOTE SERVICE BINDING：赋予或拒绝CREATE、ALTER、DROP代理服务的远程服务绑定权限。
- CREATE ROUTE：赋予或者拒绝CREATE、ALTER、DROP 代理服务路由的权限。和ALTER ANY ROUTE不同，用户必须是路由的拥有者才能删除它。
- ALTER ANY ROUTE：赋予或者拒绝CREATE、ALTER、DROP 代理服务路由的权限。
- CREATE FULLTEXT CATALOG：赋予或拒绝数据库中CREATE或DROP全文目录的权限。和ALTER ANY FULLTEXT CATALOG不同，用户必须是全文目录的拥有者才能删除它。
- ALTER ANY FULLTEXT CATALOG：赋予或拒绝CREATE、DROP或ALTER数据库内全文目录的权限，db_ddladmin固定数据库角色有此权限
- CREATE SYMMETRIC KEY：赋予或拒绝CREATE、DROP对称秘钥的权限，和ALTER ANY SYMMETRIC KEY不同，用户必须是对称秘钥的拥有者或者对对称秘钥有CONTROL权限才能删除它。
- ALTER ANY SYMMETRIC KEY：赋予或拒绝CREATE、DROP或ALTER加密使用的对称秘钥的权限，db_ddladmin固定数据库角色有此权限
- CREATE ASYMMETRIC KEY：赋予或拒绝CREATE或DROP非对称秘钥的权限。和ALTER ANY ASYMMETRIC KEY不同，用户必须是非对称秘钥的拥有者或者拥有对非对称秘钥的CONTROL权限才能删除（DROP）它。
- ALTER ANY ASYMMETRIC KEY：赋予或拒绝 CREATE、DROP或ALTER加密使用的非对称秘钥。db_ddladmin固定数据库角色有此权限
- CREATE CERTIFICATE：赋予或拒绝CREATE或DROP证书的权限。和ALTER ANY CERTIFICATE不同，用户必须为证书的拥有者或者对证书有CONTROL权限才能删除（DROP）它。
- ALTER ANY CERTIFICATE：赋予或拒绝CREATE、DROP或ALTER证书的权限。db_ddladmin固定数据库角色有此权限
- SELECT：赋予或拒绝数据库中所有使用对象发出SELECT命令的能力。最佳实践使用建议，不是在数据库级别使用该权限，而在架构级别中使用它。
- INSERT： 赋予或拒绝对数据库中所有适用对象发出INSERT命令的能力。最佳实践使用建议，不是在数据库级别使用该权限，而在架构级别中使用它。
- UPDATE： 赋予或拒绝对数据库中所有适用对象发出UPDATE命令的能力。最佳实践使用建议，不是在数据库级别使用该权限，而在架构级别中使用它。
- DELETE：赋予或拒绝对数据库中所有应用对象发起DELETE语句的权限。最佳实践使用建议，不是在数据库级别使用该权限，而在架构级别中使用它。
- REFERENCES：赋予或拒绝创建对象关系，如引用其他表的外键或者视图和函数的架构绑定使用。固定数据库角色db_ddladmin隐含此权限
- EXECUTE：赋予或拒绝对数据库中的所有应用对象发起执行权限。最佳实践使用建议，不是在数据库级别使用该权限，而在架构级别中使用它。
- ALTER ANY DATABASE DDL TRIGGER：赋予或拒绝CREATE、DROP或ALTER数据库级DDL触发器的角色（非服务器级别），db_ddladmin固定数据库角色有此权限
- CREATE DATABASE DDL EVENT NOTIFICATION：赋予或拒绝CREATE或DROP服务代理数据库事件通知的权限。和ALTER ANY DATABASE DDL EVENT NOTIFICATION不同，用户必须是DDL事件通知的拥有者才能删除它。
- ALTER ANY DATABASE EVENT NOTIFICATION：赋予或拒绝CREATE或DROP服务代理数据库事件通知的权限。
- ALTER ANY DATABASE AUDIT：赋予或拒绝CREATE、ALTER或DROP数据库审核的权限。
- VIEW DATABASE STATE：赋予或拒绝通过通过数据库级别动态视图或者函数查看当前数据库的状态权限。
- VIEW DEFINITION：赋予或拒绝数据库中查看对象的潜在T-SQL 或者元数据的权限。数据库固定角色db_securityadmin有此潜在权限。
- TAKE OWNERSHIP：赋予或拒绝转移一个用户对XML架构拥有权到另一个用户
- ALTER：赋予或拒绝修改存在的数据库权限
- CONTROL：这个权限等同于数据库拥有者权限，db_owner固定数据库角色隐含该权限
新增权限
- ALTER ANY COLUMN ENCRYPTION KEY：赋予或者拒绝创建、修改（ALTER）、删除（DROP）列加密密钥的权限（SQL Server 2016新增）。
- ALTER ANY COLUMN MASTER KEY：赋予或者拒绝创建或者删除列主密钥的权限（SQL Server 2016新增）。
- ALTER ANY DATABASE SCOPED CONFIGURATION：赋予或者拒绝更改数据库配置的权限（SQL Server 2016新增）。
- ALTER ANY EXTERNAL DATA SOURCE：赋予或拒绝修改或者删除外部数据库的权限（SQL Server 2016新增）。
- ALTER ANY EXTERNAL FILE FORMAT：赋予或拒绝创建外部文件格式的权限
- KILL DATABASE CONNECTION：赋予或拒绝包含数据库连接的KILL权限
- VIEW ANY COLUMN ENCRYPTION KEY DEFINITION：赋予或拒绝查看列秘钥定义的查看权限
- VIEW ANY COLUMN MASTER KEY DEFINITION：赋予或拒绝查看列秘钥定义查看权限
- ALTER ANY MASK：赋予或拒绝添加、替换或删除对列的屏蔽的权限，同时需要对表的 ALTER 权限（SQL Server 2016新增）。
- UNMASK
- EXECUTE ANY EXTERNAL SCRIPT：赋予或拒绝执行外部脚本的权限。
在非master库下不能给用户赋予创建数据库的权限，否则报如下错误：
只能在master 数据库中授予CREATE DATABASE 权限。
消息0，级别11，状态0，第0 行
当前命令发生了严重错误。应放弃任何可能产生的结果。
查看各个安全主题具有的权限
除数据库安全主题外，数据库中还有其他20个安全主题，分别为：
USER | ROLE | APPLICATION ROLE | ASSEMBLY | SYMMETRIC KEY | ASYMMETRIC KEY
| CERTIFICATE | DATABASE SCOPED CREDENTIAL | SCHEMA | XML SCHEMA COLLECTION | OBJECT
| FULLTEXT CATALOG | FULLTEXT STOPLIST
| MESSAGE TYPE| REMOTE SERVICE BINDING | ROUTE | CONTRACT
| SEARCH PROPERTY LIST | SERVICE
| TYPE我们可以通过系统函数 sys.fn_builtin_permissions 查到各安全主题所具有的权限，如下给出USER的查询：
SELECT *
FROM sys.fn_builtin_permissions('USER')
可以从permission_name字段中看到，可以为USER赋予或拒绝四个权限。其他安全主题可以授予的权限，有兴趣的读者可以自行查看结果。
数据库固定角色所具有的权限：
• db_owner：数据库拥有者，类似于赋予用户对数据库的CONTROL权限
• db_accessadmin
• CREATE SCHEMA
• ALTER ANY USER
• CONNECT
• db_securityadmin
• ALTER ANY APPLICATION ROLE
• ALTER ANY ROLE
• VIEW DEFINITION
• CREATE SCHEMA
• db_ddladmin
• ALTER ANY ASSEMBLY
• ALTER ANY ASYMMETRIC KEY
• ALTER ANY CERTIFICATE
• ALTER ANY CONTRACT
• ALTER ANY DATABASE DDL TRIGGER
• ALTER ANY DATABASE EVENT NOTIFICATION
• ALTER ANY DATASPACE
• ALTER ANY EXTERNAL LIBRARY
• ALTER ANY FULLTEXT CATALOG
• ALTER ANY MESSAGE TYPE
• ALTER ANY REMOTE SERVICE BINDING
• ALTER ANY ROUTE
• ALTER ANY SCHEMA
• ALTER ANY SERVICE
• ALTER ANY SYMMETRIC KEY
• CREATE AGGREGATE
• CREATE AGGREGATE
• CREATE DEFAULT
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE QUEUE
• CREATE RULE
• CREATE SYNONYM
• CREATE TABLE
• CREATE TYPE
• CREATE VIEW
• CREATE XML SCHEMA COLLECTION
• CHECKPOINT
• REFERENCES
• db_backupoperator
• BACKUP DATABASE
• BACKUP LOG
• CHECKPOINT
• db_datareader
• 赋予对数据库的SELECT权限
• db_datawriter
• 赋予对数据库的INSERT权限
• 赋予对数据库的UPDATE权限
• 赋予对数据库的DELETE权限
• db_denydatareader
• 拒绝对数据库的SELECT权限
• db_denydatawriter
• 拒绝对数据库的INSERT权限
• 拒绝对数据库的UPDATE权限
• 拒绝对数据库的DELETE权限注意数据库角色public不属于数据库固定角色，但所有数据库用户都是其成员，且无法修改。
public：在SQL Server 2006中有如下两个权限，并且可以回收
• VIEW ANY COLUMN MASTER KEY DEFINITION
• VIEW ANY COLUMN ENCRYPTION KEY DEFINITION无论SQL Server服务器级角色public，还是数据库级角色public，其最初都不能访问任何用户创建的对象。数据库角色public有许多授予系统对象的权限，这是管理内部操作所必需的。
当然，除了数据库中的这些角色外，用户还可以创建自己的数据库角色，并赋予需要的不同权限。
赋予或拒绝用户、角色、应用角色权限
赋予或拒绝用户、角色、应用角色权限语句比较简单，大体可以分为三类，下面分别给出一些示例：
--示例1：
--赋予用户对数据库安全主体的权限
GRANT SELECT TO Jack
GRANT ALTER ANY SCHEMA TO Jack WITH GRANT OPTION
--拒绝用户对数据库安全主体的权限
DENY BACKUP TO Jack
DENY BACKUP LOG TO Jack WITH GRANT OPTION为用户赋予安全主题（除数据库以外的安全主题）权限，其格式为“ON 安全主题说明:: 安全主题名”，示例如下：
--示例2：
--为用户Jack 赋予查看架构test 定义的权限
GRANT VIEW DEFINITION ON SCHEMA::test TO Jack
--赋予用户Jack 查看架构dbo下表test的权限
GRANT SELECT ON OBJECT::dbo.test TO Jack
--赋予用户Jack 查看架构dbo下表test的列a、d的权限
GRANT SELECT ON OBJECT::dbo.test(a,d) TO Jack
--赋予用户Jack 执行架构dbo下过程SimpleProc的权限
GRANT EXEC ON OBJECT::dbo.SimpleProc TO Jack为用户赋予系统对象的权限格式如下：
GRANT { SELECT | EXECUTE } ON [ sys.]system_object TO principal
当然，我们也可以通过《SQL Server 中深入理解GRANT、REVOKE 和 DENY 的区别》的方式去深入了解各种权限。
查看用户或角色所具有的权限
系统过程 sp_helprotect 可以帮助查看上面的第一种赋予或拒绝的权限
--查看哪些用户对表test有什么样的权限
EXEC sp_helprotect 'test'
--查看用户Jack有哪些权限
EXEC sp_helprotect NULL,Jack
--查看dbo授权的用户的所有权限
EXEC sp_helprotect NULL,NULL,dbo
--查看所有对象的权限
EXEC sys.sp_helprotect NULL,NULL,NULL,'o'因为系统过程sp_helprotect查看的权限不全，所以如果需要查看完整的权限，可以使用数据库权限动态视图 sys.database_permissions ，结合数据库安全主题对象视图sys.database_principals 及其他相关视图查看相关的权限，具体参考下面的脚本：
--0 = 数据库
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' TO '
+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION' ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON dp.grantee_principal_id=ds.principal_id
WHERE class=0 AND ds.principal_id=5
--1 = 对象或列
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+'ON OBJECT::'+QUOTENAME(s.name,'[')+'.'
+quotename(o.name,'[')
+CASE WHEN c.NAME IS NOT NULL THEN'('+QUOTENAME(c.name,'[')+') '
ELSE '' END
+' TO '
+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION' ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON dp.grantee_principal_id=ds.principal_id
LEFT JOIN sys.objects o
ON dp.major_id=o.object_id
LEFT JOIN sys.schemas s
ON o.schema_id=s.schema_id
LEFT JOIN sys.columns c ON dp.minor_id=c.column_id
AND dp.major_id=c.object_id
WHERE grantee_principal_id=5 AND class=1
--3 = 架构
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON SCHEMA::'
+s.name
+' TO '
+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION' ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds ON dp.grantee_principal_id=ds.principal_id
LEFT JOIN sys.schemas s ON dp.major_id=s.schema_id
WHERE class=3 AND grantee_principal_id=5
--4 = 数据库主体
--[ USER :: database_user ]
-- | [ ROLE :: database_role ]
-- | [ APPLICATION ROLE :: application_role ]
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+CASE s.type WHEN 'U' THEN ' ON USER::'
WHEN 'R' THEN ' ON ROLE::'
WHEN 'A' THEN ' ON APPLICATION ROLE::' END
+s.name
+' TO '
+QUOTENAME(d.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals s ON dp.major_id=s.principal_id
LEFT JOIN sys.database_principals d ON dp.grantee_principal_id=d.principal_id
WHERE class=4 AND grantee_principal_id=5
--5 = 程序集适用范围:SQL Server 2008通过SQL Server 2019。
SELECT * FROM sys.assemblies
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON ASSEMBLY::'
+QUOTENAME(ab.name,'[')
+' TO '
+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON dp.grantee_principal_id=ds.principal_id
LEFT JOIN sys.assemblies ab
ON dp.major_id=ab.assembly_id
WHERE class=5 AND grantee_principal_id=5
--6 = 类型
SELECT * FROM sys.types
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON TYPE::'
+QUOTENAME(s.name,'[')+'.'
+QUOTENAME(t.name,'[')
+' TO '
+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON dp.grantee_principal_id=ds.principal_id
LEFT JOIN sys.types t
ON dp.major_id=t.user_type_id
LEFT JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE class=6 AND grantee_principal_id=5
--10 = XML 架构集合的适用范围：SQL Server 2008 到SQL Server 2019。
SELECT * FROM sys.xml_schema_namespaces
SELECT * FROM sys.xml_schema_collections
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON XML SCHEMA COLLECTION::'
+QUOTENAME(xmlsc.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.xml_schema_collections xmlsc
ON dp.major_id=xmlsc.xml_collection_id
WHERE grantee_principal_id=5 AND class=10
--15 = 消息类型适用范围:SQL Server 2008通过SQL Server 2019。
--授予对Service Broker 消息类型的权限。
--SELECT * FROM sys.service_message_types
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON message type::'
+QUOTENAME(m.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.service_message_types m
ON dp.major_id=m.message_type_id
WHERE grantee_principal_id=5 AND class=15
--16 = 服务约定适用范围:SQL Server 2008通过SQL Server 2019。
SELECT * FROM sys.service_contracts
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON Contract::'
+QUOTENAME(s.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.service_contracts s
ON dp.major_id=s.service_contract_id
WHERE grantee_principal_id=5 AND class=16
--17 = 服务适用范围:SQL Server 2008通过SQL Server 2019。
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON Service::'
+QUOTENAME(s.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.services s
ON dp.major_id=s.service_id
WHERE grantee_principal_id=5 AND class=17
--19 = 的路由适用范围:SQL Server 2008通过SQL Server 2019。
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON Route::'
+QUOTENAME(r.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.routes r
ON dp.major_id=r.route_id
WHERE grantee_principal_id=5 AND class=19
--23 = 全文目录适用范围:SQL Server 2008通过SQL Server 2019。
SELECT * FROM sys.fulltext_catalogs
SELECT * FROM sys.database_permissions
WHERE class=23
--29 = 全文目录停止列表适用范围:SQL Server 2008通过SQL Server 2019。
SELECT * FROM sys.fulltext_stoplists
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON fulltext stoplist::'
+QUOTENAME(f.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.fulltext_stoplists f
ON dp.major_id=f.stoplist_id
WHERE grantee_principal_id=5 AND class=29
--24 = 对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.database_permissions dp
WHERE class=24
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON SYMMETRIC KEY::'
+QUOTENAME(asy.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.symmetric_keys asy
ON dp.major_id=asy.symmetric_key_id
WHERE grantee_principal_id=5 AND class=24
--25 = 证书适用范围:SQL Server 2008通过SQL Server 2019。
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON CERTIFICATE::'
+QUOTENAME(c.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.certificates c
ON dp.major_id=c.certificate_id
WHERE grantee_principal_id=5 AND class=25
--26 = 非对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
SELECT CASE state WHEN 'D' THEN 'DENY '
ELSE 'GRANT ' END
+permission_name COLLATE Chinese_PRC_90_CI_AI
+' ON ASYMMETRIC KEY::'
+QUOTENAME(asy.name,'[')
+' TO '+QUOTENAME(ds.name,'[')
+CASE WHEN state='W' THEN ' WITH GRANT OPTION'
ELSE '' END
FROM sys.database_permissions dp
LEFT JOIN sys.database_principals ds
ON ds.principal_id=dp.grantee_principal_id
LEFT JOIN sys.asymmetric_keys asy
ON dp.major_id=asy.asymmetric_key_id
WHERE grantee_principal_id=5 AND class=26当然这并不是最高效的查看用户权限的方式，看过我前面写的博文的同学，或者想要高效查看管理数据用户或角色权限的同学，可以通过创建《克隆SQL Server 数据库用户及权限 V1.1》中的过程 sp_DBA_userPermisionsClone ，并执行该过程来查看用户、角色或应用角色在某数据库的全部权限。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
