DENY Server Principal Permissions (Transact-SQL)
Denies permissions granted on a SQL Server login.
DENY permission [ ,...n ] } ON LOGIN ::SQL_Server_login TO <server_principal> [ ,...n ] [ CASCADE ] [ AS SQL_Server_login ] <server_principal> ::= SQL_Server_login | SQL_Server_login_from_Windows_login | SQL_Server_login_from_certificate | SQL_Server_login_from_AsymKey
Permissions at the server scope can be denied only when the current database is master.
The DENY statement will fail if CASCADE is not specified when you are denying a permission to a principal that was granted that permission with GRANT OPTION.
A SQL Server login is a server-level securable. The most specific and limited permissions that can be denied on a SQL Server login are listed in the following table, together with the more general permissions that include them by implication.
SQL Server login permission
Implied by SQL Server login permission
Implied by server permission
VIEW ANY DEFINITION
ALTER ANY LOGIN
A. Denying IMPERSONATE permission on a login
The following example denies IMPERSONATE permission on the SQL Server login WanidaBenshoof to a SQL Server login created from the Windows user AdvWorks\YoonM.
USE master; DENY IMPERSONATE ON LOGIN::WanidaBenshoof TO [AdvWorks\YoonM]; GO
B. Denying VIEW DEFINITION permission with CASCADE
The following example denies VIEW DEFINITION permission on the SQL Server login EricKurjan to SQL Server login RMeyyappan. The CASCADE option indicates that VIEW DEFINITION permission on EricKurjan will also be denied to principals to which RMeyyappan granted this permission.
USE master; DENY VIEW DEFINITION ON LOGIN::EricKurjan TO RMeyyappan CASCADE; GO