You can use GRANT, REVOKE, and DENY commands on many database objects in SQL Server.
With the GRANT command, you can authorize a user.
With the REVOKE command, you can revoke a given authorization.
With the DENY command, you can deny an authorization. In this article we will examine where we can use these commands.
With GRANT, REVOKE and DENY, you can perform many levels of authorization, canceling privileges, and denying an authorization. In this article, we will examine what we can do at the database, schema and table level.
GRANT at the database level:
With the GRANT command, we give the following permissions to the TestLogin on the TestDB database at the database level.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
use [TestDB] GO GRANT ALTER TO [TestLogin] GO GRANT ALTER ANY APPLICATION ROLE TO [TestLogin] GO GRANT ALTER ANY ASSEMBLY TO [TestLogin] GO GRANT ALTER ANY ASYMMETRIC KEY TO [TestLogin] GO GRANT ALTER ANY CERTIFICATE TO [TestLogin] GO GRANT ALTER ANY CONTRACT TO [TestLogin] GO GRANT ALTER ANY DATABASE AUDIT TO [TestLogin] GO GRANT ALTER ANY DATABASE DDL TRIGGER TO [TestLogin] GO GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [TestLogin] GO GRANT ALTER ANY DATASPACE TO [TestLogin] GO GRANT ALTER ANY EXTERNAL DATA SOURCE TO [TestLogin] GO GRANT ALTER ANY EXTERNAL FILE FORMAT TO [TestLogin] GO GRANT ALTER ANY FULLTEXT CATALOG TO [TestLogin] GO GRANT ALTER ANY MASK TO [TestLogin] GO GRANT ALTER ANY MESSAGE TYPE TO [TestLogin] GO GRANT ALTER ANY REMOTE SERVICE BINDING TO [TestLogin] GO GRANT ALTER ANY ROLE TO [TestLogin] GO GRANT ALTER ANY ROUTE TO [TestLogin] GO GRANT ALTER ANY SCHEMA TO [TestLogin] GO GRANT ALTER ANY SECURITY POLICY TO [TestLogin] GO GRANT ALTER ANY SERVICE TO [TestLogin] GO GRANT ALTER ANY SYMMETRIC KEY TO [TestLogin] GO GRANT ALTER ANY USER TO [TestLogin] GO GRANT AUTHENTICATE TO [TestLogin] GO GRANT BACKUP DATABASE TO [TestLogin] GO GRANT BACKUP LOG TO [TestLogin] GO GRANT CHECKPOINT TO [TestLogin] GO GRANT CONNECT TO [TestLogin] GO GRANT CONNECT REPLICATION TO [TestLogin] GO GRANT CONTROL TO [TestLogin] GO GRANT CREATE AGGREGATE TO [TestLogin] GO GRANT CREATE ASSEMBLY TO [TestLogin] GO GRANT CREATE ASYMMETRIC KEY TO [TestLogin] GO GRANT CREATE CERTIFICATE TO [TestLogin] GO GRANT CREATE CONTRACT TO [TestLogin] GO GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [TestLogin] GO GRANT CREATE DEFAULT TO [TestLogin] GO GRANT CREATE FULLTEXT CATALOG TO [TestLogin] GO GRANT CREATE FUNCTION TO [TestLogin] GO GRANT CREATE MESSAGE TYPE TO [TestLogin] GO GRANT CREATE PROCEDURE TO [TestLogin] GO GRANT CREATE QUEUE TO [TestLogin] GO GRANT CREATE REMOTE SERVICE BINDING TO [TestLogin] GO GRANT CREATE ROLE TO [TestLogin] GO GRANT CREATE ROUTE TO [TestLogin] GO GRANT CREATE RULE TO [TestLogin] GO GRANT CREATE SCHEMA TO [TestLogin] GO GRANT CREATE SERVICE TO [TestLogin] GO GRANT CREATE SYMMETRIC KEY TO [TestLogin] GO GRANT CREATE SYNONYM TO [TestLogin] GO GRANT CREATE TABLE TO [TestLogin] GO GRANT CREATE TYPE TO [TestLogin] GO GRANT CREATE VIEW TO [TestLogin] GO GRANT CREATE XML SCHEMA COLLECTION TO [TestLogin] GO GRANT DELETE TO [TestLogin] GO GRANT EXECUTE TO [TestLogin] GO GRANT INSERT TO [TestLogin] GO GRANT REFERENCES TO [TestLogin] GO GRANT SELECT TO [TestLogin] GO GRANT SHOWPLAN TO [TestLogin] GO GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [TestLogin] GO GRANT TAKE OWNERSHIP TO [TestLogin] GO GRANT UNMASK TO [TestLogin] GO GRANT UPDATE TO [TestLogin] GO GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [TestLogin] GO GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [TestLogin] GO GRANT VIEW DATABASE STATE TO [TestLogin] GO GRANT VIEW DEFINITION TO [TestLogin] GO |
with “WITH Grant Option“, we can grant a login to authorize other logins. Can be used in the following ways. I recommend that all authorization be performed by DBAs.
|
1 2 3 4 |
use [TestDB] GO GRANT ALTER TO [TestLogin] WITH GRANT OPTION GO |
REVOKE at the database level:
With the help of the REVOKE command, we can revoke the privileges we gave at the database level with GRANT. The use is as follows.
|
1 2 3 4 |
use [TestDB] GO REVOKE ALTER TO [TestLogin] AS [dbo] GO |
DENY at the database level:
With the DENY command, we can deny the above privileges at the database level for a user. The deny command is usually used to prevent some of the high-level privileges from being granted at a low-level. Its use is as follows.
|
1 2 3 4 |
use [TestDB] GO DENY ALTER TO [TestLogin] GO |
GRANT at Schema level:
On a schema, we can use the grant command to grant the following privileges to a user.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
use [TestDB] GO GRANT ALTER ON SCHEMA::[testschema] TO [TestLogin] GO GRANT CONTROL ON SCHEMA::[testschema] TO [TestLogin] GO GRANT CREATE SEQUENCE ON SCHEMA::[testschema] TO [TestLogin] GO GRANT DELETE ON SCHEMA::[testschema] TO [TestLogin] GO GRANT EXECUTE ON SCHEMA::[testschema] TO [TestLogin] GO GRANT INSERT ON SCHEMA::[testschema] TO [TestLogin] GO GRANT REFERENCES ON SCHEMA::[testschema] TO [TestLogin] GO GRANT SELECT ON SCHEMA::[testschema] TO [TestLogin] GO GRANT TAKE OWNERSHIP ON SCHEMA::[testschema] TO [TestLogin] GO GRANT UPDATE ON SCHEMA::[testschema] TO [TestLogin] GO GRANT VIEW CHANGE TRACKING ON SCHEMA::[testschema] TO [TestLogin] GO GRANT VIEW DEFINITION ON SCHEMA::[testschema] TO [TestLogin] GO |
Revoke at schema level:
With the Revoke command, we can revoke the privileges granted at the schema level. Its use is as follows.
|
1 2 3 4 |
use [TestDB] GO REVOKE ALTER ON SCHEMA::[testschema] TO [TestLogin] AS [dbo] GO |
DENY at schema level:
With the DENY command, we can deny the above privileges at the schema level for a user. The deny command is usually used to prevent some of the high-level privileges from being granted at a low-level. Its use is as follows.
|
1 2 3 4 |
use [TestDB] GO DENY ALTER ON SCHEMA::[testschema] TO [TestLogin] GO |
GRANT at table level:
We can use the GRANT command on a table to give the following privileges to a user.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
use [TestDB] GO GRANT ALTER ON [dbo].[MyTable] TO [TestLogin] GO GRANT CONTROL ON [dbo].[MyTable] TO [TestLogin] GO GRANT DELETE ON [dbo].[MyTable] TO [TestLogin] GO GRANT INSERT ON [dbo].[MyTable] TO [TestLogin] GO GRANT REFERENCES ON [dbo].[MyTable] TO [TestLogin] GO GRANT SELECT ON [dbo].[MyTable] TO [TestLogin] GO GRANT TAKE OWNERSHIP ON [dbo].[MyTable] TO [TestLogin] GO GRANT UPDATE ON [dbo].[MyTable] TO [TestLogin] GO GRANT VIEW CHANGE TRACKING ON [dbo].[MyTable] TO [TestLogin] GO GRANT VIEW DEFINITION ON [dbo].[MyTable] TO [TestLogin] GO |
Revoke at table level:
With the Revoke command, we can revoke the privileges granted at the table level. Its use is as follows.
|
1 2 3 4 |
use [TestDB] GO REVOKE ALTER ON [dbo].[MyTable] TO [TestLogin] AS [dbo] GO |
DENY at table level:
With the DENY command, we can deny the above privileges at the table level for a user. The deny command is usually used to prevent some of the high-level privileges from being granted at a low-level. Its use is as follows.
|
1 2 3 4 |
use [TestDB] GO DENY ALTER ON [dbo].[MyTable] TO [TestLogin] GO |
