DENY 数据库权限 (Transact-SQL)
拒绝对数据库的权限。
语法
DENY <permission> [ ,...n ]
TO <database_principal> [ ,...n ] [ CASCADE ]
[ AS <database_principal> ]
<permission> ::=
permission | ALL [ PRIVILEGES ]
<database_principal> ::=
Database_user
| Database_role
| Application_role
| Database_user_mapped_to_Windows_User
| Database_user_mapped_to_Windows_Group
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_key
| Database_user_with_no_login
参数
permission
指定可对数据库拒绝的权限。 有关权限的列表,请参阅本主题后面的“备注”部分。ALL
该选项不会拒绝所有可能的权限。 拒绝 ALL 等同于拒绝下列权限:BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和 CREATE VIEW。PRIVILEGES
包含此参数是为了符合 ISO 标准。 请不要更改 ALL 的行为。CASCADE
指示要拒绝的权限也会被对指定主体授予权限的主体拒绝。AS <database_principal>
指定执行此查询的主体要从哪个主体派生其拒绝该权限的权利。Database_user
指定数据库用户。Database_role
指定数据库角色。Application_role
指定应用程序角色。Database_user_mapped_to_Windows_User
指定映射到 Windows 用户的数据库用户。Database_user_mapped_to_Windows_Group
指定映射到 Windows 组的数据库用户。Database_user_mapped_to_certificate
指定映射到证书的数据库用户。Database_user_mapped_to_asymmetric_key
指定映射到非对称密钥的数据库用户。Database_user_with_no_login
指定无相应服务器级主体的数据库用户。
注释
数据库是安全对象,包含于权限层次结构中作为其父级的服务器中。 下表列出了可拒绝的对数据库最为具体的限定权限,以及隐含这些权限的更为通用的权限。
数据库权限 |
数据库权限隐含的权限 |
服务器权限隐含的权限 |
---|---|---|
ALTER |
CONTROL |
ALTER ANY DATABASE |
ALTER ANY APPLICATION ROLE |
ALTER |
CONTROL SERVER |
ALTER ANY ASSEMBLY |
ALTER |
CONTROL SERVER |
ALTER ANY ASYMMETRIC KEY |
ALTER |
CONTROL SERVER |
ALTER ANY CERTIFICATE |
ALTER |
CONTROL SERVER |
ALTER ANY CONTRACT |
ALTER |
CONTROL SERVER |
ALTER ANY DATABASE AUDIT |
ALTER |
ALTER ANY SERVER AUDIT |
ALTER ANY DATABASE DDL TRIGGER |
ALTER |
CONTROL SERVER |
ALTER ANY DATABASE EVENT NOTIFICATION |
ALTER |
ALTER ANY EVENT NOTIFICATION |
ALTER ANY DATASPACE |
ALTER |
CONTROL SERVER |
ALTER ANY FULLTEXT CATALOG |
ALTER |
CONTROL SERVER |
ALTER ANY MESSAGE TYPE |
ALTER |
CONTROL SERVER |
ALTER ANY REMOTE SERVICE BINDING |
ALTER |
CONTROL SERVER |
ALTER ANY ROLE |
ALTER |
CONTROL SERVER |
ALTER ANY ROUTE |
ALTER |
CONTROL SERVER |
ALTER ANY SCHEMA |
ALTER |
CONTROL SERVER |
ALTER ANY SERVICE |
ALTER |
CONTROL SERVER |
ALTER ANY SYMMETRIC KEY |
ALTER |
CONTROL SERVER |
ALTER ANY USER |
ALTER |
CONTROL SERVER |
AUTHENTICATE |
CONTROL |
AUTHENTICATE SERVER |
BACKUP DATABASE |
CONTROL |
CONTROL SERVER |
BACKUP LOG |
CONTROL |
CONTROL SERVER |
CHECKPOINT |
CONTROL |
CONTROL SERVER |
CONNECT |
CONNECT REPLICATION |
CONTROL SERVER |
CONNECT REPLICATION |
CONTROL |
CONTROL SERVER |
CONTROL |
CONTROL |
CONTROL SERVER |
CREATE AGGREGATE |
ALTER |
CONTROL SERVER |
CREATE ASSEMBLY |
ALTER ANY ASSEMBLY |
CONTROL SERVER |
CREATE ASYMMETRIC KEY |
ALTER ANY ASYMMETRIC KEY |
CONTROL SERVER |
CREATE CERTIFICATE |
ALTER ANY CERTIFICATE |
CONTROL SERVER |
CREATE CONTRACT |
ALTER ANY CONTRACT |
CONTROL SERVER |
CREATE DATABASE |
CONTROL |
CREATE ANY DATABASE |
CREATE DATABASE DDL EVENT NOTIFICATION |
ALTER ANY DATABASE EVENT NOTIFICATION |
CREATE DDL EVENT NOTIFICATION |
CREATE DEFAULT |
ALTER |
CONTROL SERVER |
CREATE FULLTEXT CATALOG |
ALTER ANY FULLTEXT CATALOG |
CONTROL SERVER |
CREATE FUNCTION |
ALTER |
CONTROL SERVER |
CREATE MESSAGE TYPE |
ALTER ANY MESSAGE TYPE |
CONTROL SERVER |
CREATE PROCEDURE |
ALTER |
CONTROL SERVER |
CREATE QUEUE |
ALTER |
CONTROL SERVER |
CREATE REMOTE SERVICE BINDING |
ALTER ANY REMOTE SERVICE BINDING |
CONTROL SERVER |
CREATE ROLE |
ALTER ANY ROLE |
CONTROL SERVER |
CREATE ROUTE |
ALTER ANY ROUTE |
CONTROL SERVER |
CREATE RULE |
ALTER |
CONTROL SERVER |
CREATE SCHEMA |
ALTER ANY SCHEMA |
CONTROL SERVER |
CREATE SERVICE |
ALTER ANY SERVICE |
CONTROL SERVER |
CREATE SYMMETRIC KEY |
ALTER ANY SYMMETRIC KEY |
CONTROL SERVER |
CREATE SYNONYM |
ALTER |
CONTROL SERVER |
CREATE TABLE |
ALTER |
CONTROL SERVER |
CREATE TYPE |
ALTER |
CONTROL SERVER |
CREATE VIEW |
ALTER |
CONTROL SERVER |
CREATE XML SCHEMA COLLECTION |
ALTER |
CONTROL SERVER |
DELETE |
CONTROL |
CONTROL SERVER |
EXECUTE |
CONTROL |
CONTROL SERVER |
INSERT |
CONTROL |
CONTROL SERVER |
KILL DATABASE CONNECTION 仅适用于 Windows Azure SQL Database。 |
CONTROL |
ALTER ANY CONNECTION |
REFERENCES |
CONTROL |
CONTROL SERVER |
SELECT |
CONTROL |
CONTROL SERVER |
SHOWPLAN |
CONTROL |
ALTER TRACE |
SUBSCRIBE QUERY NOTIFICATIONS |
CONTROL |
CONTROL SERVER |
TAKE OWNERSHIP |
CONTROL |
CONTROL SERVER |
UPDATE |
CONTROL |
CONTROL SERVER |
VIEW DATABASE STATE |
CONTROL |
VIEW SERVER STATE |
VIEW DEFINITION |
CONTROL |
VIEW ANY DEFINITION |
权限
执行此语句的主体(或用 AS 选项指定的主体)必须具有对数据库的 CONTROL 权限,或具有隐含对数据库的 CONTROL 权限的更高权限。
若要使用 AS 选项,则指定的主体必须拥有数据库。
示例
A.拒绝创建证书的权限
以下示例拒绝用户 MelanieK 对 AdventureWorks2012 数据库的 CREATE CERTIFICATE 权限。
USE AdventureWorks2012;
DENY CREATE CERTIFICATE TO MelanieK;
GO
B.对应用程序角色拒绝 REFERENCES 权限
以下示例拒绝应用程序角色 AuditMonitor 对 AdventureWorks2012 数据库的 REFERENCES 权限。
USE AdventureWorks2012;
DENY REFERENCES TO AuditMonitor;
GO
C.使用 CASCADE 拒绝 VIEW DEFINITION
以下示例拒绝用户 CarmineEs 以及 CarmineEs 已授予 VIEW DEFINITION 权限的所有主体对 AdventureWorks2012 数据库的 VIEW DEFINITION 权限。
USE AdventureWorks2012;
DENY VIEW DEFINITION TO CarmineEs CASCADE;
GO
请参阅
参考
sys.database_permissions (Transact-SQL)
sys.database_principals (Transact-SQL)
CREATE DATABASE (Transact-SQL)