How to GRANT Permissions in T-SQL

Introduction

To follow the Principle of Least Privilege you need to at some point grant specific permissions to a login or user. While it is often a lot simpler to just add a user to the db_owner fixed database role or a login to the sysadmin fixed server role, being more granular with the permissions will lead to a better protected and more secure system.

GRANT Example

To grant a permission to a database principal we can use the GRANT statement. The GRANT statement, while it looks fairly innocent, is actually quite complex. This shows in the number of pages dedicated to it in Books Online.

Today I am going to look at just the basic use case. The GRANT statement basically has three sections:

1. What can be done?
2. Where (or to what) can it be done?
3. Who can do it?

The syntax looks like this:

The question "What can be done?" is answered by the word SELECT. It describes the permission to be granted. In this case we are allowing for data to be selected.

The answer to the "Where or to what can it be done?" question is given by OBJECT::dbo.tst. It describes the securable; in this case the table or view that data can be selected from. The prefix OBJECT:: describes the type of the securable. If, as it is in this example, the securable is a database object like a table, the prefix is optional. In other cases like an entire schema the prefix must be specified.

Finally, TestUser1 is the grantee and describes the principal that will be able to execute the action after the GRANT statement was executed.

So, the above statement allows the database principal TestUser1 to execute a SELECT against the table or view dbo.tst.

GRANT in Action

Let's see this in action. As described above, we have a database principal TestUser1 and a table or view dbo.tst. Before the grant has been executed, an attempt to select from that object fails:

However, after the GRANT statement was executed, the SELECT statement works as expected:

Server Level Securables

The syntax described above works for almost all securables. There are however a few securables that are not live in a user database, like a login. When executing a GRANT statement to grant a permission on such a server level securable, the current database must be master. Otherwise an error is returned:

Also, if the securable is at the server level, the grantee (the principal that the permission is granted to) must be a server principal. If on the other hand the securable is a database object or a database itself, the grantee must be a database principal in that same database.

Finally be aware that if the securable is the instance itself, the securable does not need to be mentioned in the GRANT statement. It is implied by the permission type, as for example in the case of the CONTROLL SERVER permission:

Summary

The GRANT statement can be used to give fine grained permissions to database and server principals. It is a surprisingly complex statement. In the basic form I has three sections that can be described by the three questions "What?", "Where?" and "Who?" They describe the permission, the securable and the grantee in that order.