DENY (Transact-SQL)DENY (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

拒绝为主体授予权限。Denies a permission to a principal. 防止该主体通过组或角色成员身份继承权限。Prevents that principal from inheriting the permission through its group or role memberships. DENY 优先于所有权限,但 DENY 不适用于 sysadmin 固定服务器角色的对象所有者或成员。DENY takes precedence over all permissions, except that DENY does not apply to object owners or members of the sysadmin fixed server role. 安全性注意事项:sysadmin 固定服务器角色的成员和对象所有者不能拒绝权限。Security Note Members of the sysadmin fixed server role and object owners cannot be denied permissions."

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
-- Simplified syntax for DENY  
DENY   { ALL [ PRIVILEGES ] } 
     | <permission>  [ ( column [ ,...n ] ) ] [ ,...n ]  
    [ ON [ <class> :: ] securable ] 
    TO principal [ ,...n ]   
    [ CASCADE] [ AS principal ]  
[;]

<permission> ::=  
{ see the tables below }  
  
<class> ::=  
{ see the tables below }  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
DENY   
    <permission> [ ,...n ]  
    [ ON [ <class_> :: ] securable ]   
    TO principal [ ,...n ]  
    [ CASCADE ]  
[;]  
  
<permission> ::=  
{ see the tables below }  
  
<class> ::=  
{  
      LOGIN  
    | DATABASE  
    | OBJECT  
    | ROLE  
    | SCHEMA  
    | USER  
}  

参数Arguments

ALLALL
该选项不拒绝所有可能权限。This option does not deny all possible permissions. 拒绝 ALL 相当于拒绝下列权限。Denying ALL is equivalent to denying the following permissions.

  • 如果安全对象是数据库,则 ALL 对应 BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和 CREATE VIEW。If the securable is a database, ALL means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.

  • 如果安全对象是标量函数,则 ALL 对应 EXECUTE 和 REFERENCES。If the securable is a scalar function, ALL means EXECUTE and REFERENCES.

  • 如果安全对象是表值函数,则 ALL 对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。If the securable is a table-valued function, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

  • 如果安全对象是存储过程,则 ALL 表示 EXECUTE。If the securable is a stored procedure, ALL means EXECUTE.

  • 如果安全对象是表,则 ALL 对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。If the securable is a table, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

  • 如果安全对象是视图,则 ALL 对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。If the securable is a view, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

备注

不推荐使用 DENY ALL 语法。The DENY ALL syntax is deprecated. 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.应改为拒绝特定权限。Deny specific permissions instead.

PRIVILEGESPRIVILEGES
包含此参数是为了符合 ISO 标准。Included for ISO compliance. 请不要更改 ALL 的行为。Does not change the behavior of ALL.

permissionpermission
权限的名称。Is the name of a permission. 下面列出的子主题介绍了不同权限与安全对象之间的有效映射。The valid mappings of permissions to securables are described in the sub-topics listed below.

columncolumn
指定表中拒绝授予其权限的列名。Specifies the name of a column in a table on which permissions are being denied. 需要使用圆括号 ()。The parentheses () are required.

classclass
指定拒绝授予其权限的安全对象的类。Specifies the class of the securable on which the permission is being denied. 需要使用作用域限定符 ::。The scope qualifier :: is required.

securablesecurable
指定拒绝授予其权限的安全对象。Specifies the securable on which the permission is being denied.

TO principalTO principal
主体的名称。Is the name of a principal. 可以对其拒绝安全对象权限的主体随安全对象而异。The principals to which permissions on a securable can be denied vary, depending on the securable. 有关有效的组合,请参阅下面列出的特定于安全对象的主题。See the securable-specific topics listed below for valid combinations.

CASCADECASCADE
指示拒绝授予指定主体该权限,同时,对该主体授予了该权限的所有其他主体,也拒绝授予该权限。Indicates that the permission is denied to the specified principal and to all other principals to which the principal granted the permission. 当主体具有带 GRANT OPTION 的权限时,为必选项。Required when the principal has the permission with GRANT OPTION.

AS principalAS principal
指定执行此查询的主体要从哪个主体派生其拒绝该权限的权利。Specifies the principal from which the principal executing this query derives its right to deny the permission. 使用 AS principal 子句指示:记录为权限的拒绝者的主体应为执行该语句的用户以外的主体。Use the AS principal clause to indicate that the principal recorded as the denier of the permission should be a principal other than the person executing the statement. 例如,假设用户 Mary 是 principal_id 12,用户 Raul 是主体 15。For example, presume that user Mary is principal_id 12 and user Raul is principal 15. Mary 执行 DENY SELECT ON OBJECT::X TO Steven WITH GRANT OPTION AS Raul; 现在,即使语句的实际执行者是用户 13 (Mary),sys.database_permissions 表仍将指示 deny 语句的 grantor_prinicpal_id 为 15 (Raul)。Mary executes DENY SELECT ON OBJECT::X TO Steven WITH GRANT OPTION AS Raul; Now the sys.database_permissions table will indicate that the grantor_prinicpal_id of the deny statement was 15 (Raul) even though the statement was actually executed by user 13 (Mary).

在此语句中使用 AS 并不意味着能够模拟其他用户。The use of AS in this statement does not imply the ability to impersonate another user.

RemarksRemarks

DENY 语句的完整语法很复杂。The full syntax of the DENY statement is complex. 上面的语法关系图进行了简化以突出其结构。The syntax diagram above was simplified to draw attention to its structure. 下列主题说明了用于拒绝授予特定安全对象的权限的完整语法。Complete syntax for denying permissions on specific securables is described in the topics listed below.

如果在拒绝为主体授予某种权限时未指定 CASCADE,而之前为该主体授予此权限时指定了 GRANT OPTION,则 DENY 将失败。DENY will fail if CASCADE is not specified when denying a permission to a principal that was granted that permission with GRANT OPTION specified.

sp_helprotect 系统存储过程可报告对数据库级安全对象的权限。The sp_helprotect system stored procedure reports permissions on a database-level securable.

注意

表级 DENY 并不优先于列级 GRANT。A table-level DENY does not take precedence over a column-level GRANT. 保留了权限层次结构中这种不一致性以保持向后兼容。This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. 未来的版本会将其删除。It will be removed in a future release.

注意

拒绝授予数据库 CONTROL 权限将隐式拒绝授予该数据库 CONNECT 权限。Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. 如果拒绝授予某一主体对某一数据库的 CONTROL 权限,该主体将无法连接到该数据库。A principal that is denied CONTROL permission on a database will not be able to connect to that database.

注意

拒绝授予 CONTROL SERVER 权限将隐式拒绝授予对服务器的 CONNECT SQL 权限。Denying CONTROL SERVER permission implicitly denies CONNECT SQL permission on the server. 如果拒绝授予某一主体对某一服务器的 CONTROL SERVER 权限,该主体将无法连接到该服务器。A principal that is denied CONTROL SERVER permission on a server will not be able to connect to that server.

权限Permissions

调用方(或使用 AS 选项指定的主体)必须对安全对象具有 CONTROL 权限,或对该安全对象具有隐含 CONTROL 权限的更高权限。The caller (or the principal specified with the AS option) must have either CONTROL permission on the securable, or a higher permission that implies CONTROL permission on the securable. 如果使用 AS 选项,那么指定主体必须拥有其权限被拒绝授予的安全对象。If using the AS option, the specified principal must own the securable on which a permission is being denied.

被授予 CONTROL SERVER 权限的用户(如 sysadmin 固定服务器角色的成员)可以拒绝对服务器中任何安全对象授予权限。Grantees of CONTROL SERVER permission, such as members of the sysadmin fixed server role, can deny any permission on any securable in the server. 被授予数据库 CONTROL 权限的用户(如 db_owner 固定数据库角色的成员)可以拒绝对数据库中任何安全对象授予权限。Grantees of CONTROL permission on the database, such as members of the db_owner fixed database role, can deny any permission on any securable in the database. 被授予架构 CONTROL 权限的用户可以拒绝对架构中任何对象授予权限。Grantees of CONTROL permission on a schema can deny any permission on any object in the schema. 如果使用 AS 子句,那么指定主体必须拥有其权限被拒绝授予的安全对象。If the AS clause is used, the specified principal must own the securable on which permissions are being denied.

示例Examples

下表列出了安全对象以及描述特定于安全对象的语法的主题。The following table lists the securables and the topics that describe the securable-specific syntax.

应用程序角色Application Role DENY 数据库主体权限 (Transact-SQL)DENY Database Principal Permissions (Transact-SQL)
AssemblyAssembly DENY 程序集权限 (Transact-SQL)DENY Assembly Permissions (Transact-SQL)
非对称密钥Asymmetric Key DENY 非对称密钥权限 (Transact-SQL)DENY Asymmetric Key Permissions (Transact-SQL)
可用性组Availability Group DENY 可用性组权限 (Transact-SQL)DENY Availability Group Permissions (Transact-SQL)
证书Certificate DENY 证书权限 (Transact-SQL)DENY Certificate Permissions (Transact-SQL)
约定Contract DENY Service Broker 权限 (Transact-SQL)DENY Service Broker Permissions (Transact-SQL)
“数据库”Database DENY 数据库权限 (Transact-SQL)DENY Database Permissions (Transact-SQL)
数据库作用域凭据Database Scoped Credential DENY 数据库作用域凭据 (Transact-SQL)DENY Database Scoped Credential (Transact-SQL)
端点Endpoint DENY 终结点权限 (Transact-SQL)DENY Endpoint Permissions (Transact-SQL)
全文目录Full-Text Catalog DENY 全文权限 (Transact-SQL)DENY Full-Text Permissions (Transact-SQL)
全文非索引字表Full-Text Stoplist DENY 全文权限 (Transact-SQL)DENY Full-Text Permissions (Transact-SQL)
函数Function DENY 对象权限 (Transact-SQL)DENY Object Permissions (Transact-SQL)
登录Login DENY 服务器主体权限 (Transact-SQL)DENY Server Principal Permissions (Transact-SQL)
消息类型Message Type DENY Service Broker 权限 (Transact-SQL)DENY Service Broker Permissions (Transact-SQL)
ObjectObject DENY 对象权限 (Transact-SQL)DENY Object Permissions (Transact-SQL)
队列Queue DENY 对象权限 (Transact-SQL)DENY Object Permissions (Transact-SQL)
远程服务绑定Remote Service Binding DENY Service Broker 权限 (Transact-SQL)DENY Service Broker Permissions (Transact-SQL)
角色Role DENY 数据库主体权限 (Transact-SQL)DENY Database Principal Permissions (Transact-SQL)
路由Route DENY Service Broker 权限 (Transact-SQL)DENY Service Broker Permissions (Transact-SQL)
架构Schema DENY 架构权限 (Transact-SQL)DENY Schema Permissions (Transact-SQL)
搜索属性列表Search Property List DENY 搜索属性列表权限 (Transact-SQL)DENY Search Property List Permissions (Transact-SQL)
“服务器”Server DENY 服务器权限 (Transact-SQL)DENY Server Permissions (Transact-SQL)
服务Service DENY Service Broker 权限 (Transact-SQL)DENY Service Broker Permissions (Transact-SQL)
存储过程Stored Procedure DENY 对象权限 (Transact-SQL)DENY Object Permissions (Transact-SQL)
对称密钥Symmetric Key DENY 对称密钥权限 (Transact-SQL)DENY Symmetric Key Permissions (Transact-SQL)
同义词Synonym DENY 对象权限 (Transact-SQL)DENY Object Permissions (Transact-SQL)
系统对象System Objects DENY 系统对象权限 (Transact-SQL)DENY System Object Permissions (Transact-SQL)
Table DENY 对象权限 (Transact-SQL)DENY Object Permissions (Transact-SQL)
类型Type DENY 类型权限 (Transact-SQL)DENY Type Permissions (Transact-SQL)
用户User DENY 数据库主体权限 (Transact-SQL)DENY Database Principal Permissions (Transact-SQL)
“查看”View DENY 对象权限 (Transact-SQL)DENY Object Permissions (Transact-SQL)
XML 架构集合XML Schema Collection DENY XML 架构集合权限 (Transact-SQL)DENY XML Schema Collection Permissions (Transact-SQL)

另请参阅See Also

REVOKE (Transact-SQL) REVOKE (Transact-SQL)
sp_addlogin (Transact-SQL) sp_addlogin (Transact-SQL)
sp_adduser (Transact-SQL) sp_adduser (Transact-SQL)
sp_changedbowner (Transact-SQL) sp_changedbowner (Transact-SQL)
sp_dropuser (Transact-SQL) sp_dropuser (Transact-SQL)
sp_helprotect (Transact-SQL) sp_helprotect (Transact-SQL)
sp_helpuser (Transact-SQL)sp_helpuser (Transact-SQL)