All the demo code so far has supposed that you are authorized inside a database as the dbo user. This user has all possible permissions inside a database. However, in real life it might be necessary for other users to create and modify objects. These users could be developers or other database administrators. To modify objects, they need statement permissions. Statement permissions are on the server, database, schema, or at the object level, depending on which level you work at. In addition, end users must use objects, and thus need object permissions. Object permissions depend on the type of the object you are working with.

Statement permissions include permissions to use any DDL statements (that is, to create, alter, and drop objects). Object permissions include permissions to use objects (that is, to use the Data Modification Language (DML) statements). However, the two permissions classes slightly overlap, and you can treat a couple of permissions as both statement and object permissions.

You control permissions by using these DCL elements: the GRANT, REVOKE, and DENY statements. You already know that without explicitly granted permission, a user cannot use an object. You give the permissions by using the GRANT statement. You explicitly prohibit the usage of an object by using the DENY statement. You clear an explicit GRANT or an explicit DENY permission by using the REVOKE statement. You might wonder why you need an explicitly DENY statement when, without an explicit GRANT, a user cannot use an object. The DENY statement exists because all grants are cumulative. For example, if a user gets a GRANT permission to select from table1 and the role that the user is a member of is granted permission to select from table2, the user can select from both tables. If you want to be sure that the user can never select from table2, you should deny the select permission from table2 to this user. A DENY for an ordinary user always supersedes every GRANT.

You cannot grant, deny, or revoke permissions to or from special roles at the server or database level. For example, you cannot deny anything inside a database to the db_owner role. You cannot grant, deny, or revoke permissions to special logins and database users (that is, to sa, dbo, INFORMATION_SCHEMA, and sys). Finally, you cannot grant, deny, or revoke permissions to yourself.

Statement permissions let users create and alter objects, or back up a database and transaction log. Permissions granted on a higher level include implicit permissions on a lower level. For example, permissions granted at the schema level are implicitly granted on all objects in the schema. In addition, there is a hierarchy between permissions on the same level; some are stronger and implicitly include weaker permissions. The CONTROL permission is the strongest. For example, the CONTROL permission on the database level implies all other permissions on the same database. Therefore, you have two different kinds of hierarchy: a hierarchy between securables and a hierarchy between permissions. You can treat high-level permissions as covering the more detailed, low-level permissions that they imply. For example, if a user needs to alter an object, the user needs either the ALTER OBJECT permission or any other higher permission, such as the ALTER ANY SCHEMA permission.

The types of permission depend on the types of database object. You can get a list of permissions applicable for an object or objects by using the sys.fn_builtin_permissions system function. For example, you can check which permissions are applicable for user-defined types, or check the objects for which the SELECT permission is applicable, like the following two queries do:

SELECT * FROM sys.fn_builtin_permissions(N'TYPE'); 
SELECT * FROM sys.fn_builtin_permissions(DEFAULT)  
WHERE permission_name = N'SELECT'; 
GO 

In SQL Server, you can specify very detailed permissions. For example, you can specify that a user can select or update only some columns of a table. Specifying permissions on such a granular level means a lot of administrative work, and is nearly impossible to do in a limited time with graphical tools such as SSMS. You should rarely go that far.

The GRANT statement includes the WITH GRANT OPTION. This option indicates that the principal to whom you grant permission on an object can grant this permission on the same object to other principals.

The DENY statement comes with the CASCADE option. When you use this option with the DENY statement, you indicate that the permission you are denying is also denied to other principals to which it has been granted by this principal.

The REVOKE statement has the GRANT OPTION FOR and the CACSCADE options. GRANT OPTION FOR means you are revoking the permission to grant the same permission to other principals (that is, you are revoking the WITH GRANT OPTION permission you gave to this principal by using the GRANT statement). The CASCADE option means you are revoking a permission not just from the principal you mention in the statement but also from other principals to which permission has been granted by this principal. Note that such a cascading revocation revokes both the GRANT and DENY of that permission.

The following code shows how to use object permissions. First, the code grants the CONTROL permission on dbo.Table1 to LoginB. LoginB can read the table:

GRANT CONTROL ON dbo.Table1 TO LoginB; 
GO 
EXECUTE AS USER = 'LoginB'; 
SELECT * 
FROM dbo.Table1; 
REVERT; 
GO 

Next, you deny the SELECT permission on dbo.Table1 to LoginB. Note that LoginB still has the CONTROL permission on this table, so this user can insert into the table:

DENY SELECT ON dbo.Table1 TO LoginB; 
GO 
EXECUTE AS USER = 'LoginB'; 
INSERT INTO dbo.Table1(id, tableContainer) 
VALUES (2, 'dbo'); 
REVERT; 
GO 

However, you denied the SELECT permission to LoginB. An explicit DENY for an ordinary user always supersedes every explicit GRANT. Therefore, the following code produces an error, stating that the SELECT permission is denied:

EXECUTE AS USER = 'LoginB'; 
SELECT * 
FROM dbo.Table1; 
REVERT; 
GO 

Finally, security would not be worth much if a user could change their own settings. The following code impersonates LoginB and tries to change the permissions to the same database user:

EXECUTE AS USER = 'LoginB'; 
REVOKE SELECT ON dbo.Table1 FROM LoginB; 
REVERT; 
GO 

Of course, the previous code produced an error. However, you, as the dbo database user, can change the permissions for the user LoginB, and therefore the following code succeeds:

REVOKE SELECT ON dbo.Table1 FROM LoginB; 
GO