Skip to content

Latest commit

 

History

History
101 lines (82 loc) · 4.35 KB

deny-schema-permissions-transact-sql.md

File metadata and controls

101 lines (82 loc) · 4.35 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords dev_langs
DENY Schema Permissions (Transact-SQL)
DENY Schema Permissions (Transact-SQL)
VanMSFT
vanto
03/14/2017
sql
t-sql
reference
denying permissions [SQL Server], schemas
schemas [SQL Server], permissions
permissions [SQL Server], schemas
DENY statement, schemas
TSQL

DENY Schema Permissions (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

Denies permissions on a schema.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

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

[!INCLUDEsql-server-tsql-previous-offline-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)