DENY Schema Permissions (Transact-SQL)
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 ]
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)