Edit

DENY Schema Permissions (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Denies permissions on a schema.

Transact-SQL syntax conventions

Syntax

DENY permission  [ ,...n ] } ON SCHEMA :: schema_name  
    TO database_principal [ ,...n ]   
    [ CASCADE ]  
        [ AS denying_principal ]

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Arguments

permission
Specifies a permission that can be denied on a schema. For a list of these permissions, see the Remarks section later in this article.

ON SCHEMA :: schema*_name*
Specifies the schema on which the permission is denied. The scope qualifier :: is required.

database_principal
Specifies the principal to which the permission is denied. database_principal can be one of these principals:

  • Database user
  • Database role
  • Application role
  • Database user mapped to a Windows login
  • Database user mapped to a Windows group
  • Database user mapped to a certificate
  • Database user mapped to an asymmetric key
  • Database user not mapped to a server principal

CASCADE
Denies permission to any other principals that the specified database_principal granted permission to.

denying_principal
Specifies a principal from which the principal executing this query derives its right to deny the permission. denying_principal can be one of these principals:

  • Database user
  • Database role
  • Application role
  • Database user mapped to a Windows login
  • Database user mapped to a Windows group
  • Database user mapped to a certificate
  • Database user mapped to an asymmetric key
  • Database user not mapped to a server principal

Remarks

A schema is a database-level securable. It's contained by the database that is its parent in the permissions hierarchy. The most specific and limited permissions that can be denied on a schema are listed in the following table. The table shows the more general permissions that include them by implication.

Schema permission Implied by schema permission Implied by database permission
ALTER CONTROL ALTER ANY SCHEMA
CONTROL CONTROL CONTROL
CREATE SEQUENCE ALTER ALTER ANY SCHEMA
DELETE CONTROL DELETE
EXECUTE CONTROL EXECUTE
INSERT CONTROL INSERT
REFERENCES CONTROL REFERENCES
SELECT CONTROL SELECT
TAKE OWNERSHIP CONTROL CONTROL
UPDATE CONTROL UPDATE
VIEW CHANGE TRACKING CONTROL CONTROL
VIEW DEFINITION CONTROL VIEW DEFINITION

Permissions

Requires CONTROL permission on the schema. If you're using the AS option, the specified principal must own the schema.

See Also

CREATE SCHEMA (Transact-SQL)
DENY (Transact-SQL)
Permissions (Database Engine)
Principals (Database Engine)
sys.fn_builtin_permissions (Transact-SQL)
sys.fn_my_permissions (Transact-SQL)
HAS_PERMS_BY_NAME (Transact-SQL)