SQL Server Permission

Introduction of SQL Server Permission

SQL Server has more than 230 permissions that can be granted to a principal. By principal, we mean different logins, user groups and server roles. The permissions can range from allowing a user to CREATE, ALTER or MODIFY a database object to restricting it to only SELECT from it. The administrator can play with these permissions and GRANT, REVOKE or DENY them to various user logins, groups or servers based on the requirement. These permissions in SQL SERVER helps in maintaining the security of the databases.

Here are a few database-level permissions in SQL Server :

• CREATE DATABASE: It is a database level permission that lets a user create or restore a database. This permission can only be granted in the master database.
•  ALTER ANY <>: With this permission, a user can alter any application role, column, key, database trigger, security policy, database name, etc. This permission is usually given to database admins.
• CREATE <> : It is permission that lets a user to create <functions,procedures, tables, aggregate, rule> etc. in a database.
• DELETE | EXECUTE| INSERT| SELECT| REFERENCES| UPDATE: These are few other permissions that a database owner or admin can grant to a user. They apply to schema level objects in the database.
• BACKUP DATABASE| LOG| CHECKPOINT: Such permissions enables a user to take a backup of the database, create checkpoints, etc. They are usually assigned to backup operators and other trustworthy roles.
• CONTROL DATABASE: This permission is the most crucial, and this is only with db_owner by default. It lets the owner drop or delete a database.

One must note that the db_owner role in SQL Server, by default has all the permissions on the database. It is quite intuitive since you are the owner you should have all the permissions. By now, we have a fair idea of what permissions are in SQL Server. The other thing which must be coming in your head is, who manages all these permissions and how are they granted or revoked? You will find answers to all these questions, hang in there till the end of this article.

If you have created the database, you are the owner and the entire control is in your hands. It’s up to you to whom you want to grant permissions. You can also delegate this task of managing permissions to another user by giving it some top level permissions. In SQL Server, we have three commands namely, GRANT, REVOKE and DENY that lets an admin or owner manage permissions. Here the syntax for all three of these commands.

Syntax and Parameters

Syntax and parameters of sql server permission are given below:

GRANT Permission Syntax

GRANT <permission>
ON  database_object
TO role
[WITH GRANT OPTION];

The parameters used in the above-mentioned syntax are as follow :

• permission: Mention the permissions such as CREATE, EXECUTE, DELETE, ALTER, UPDATE, etc. which you wish to grant to a user_role.
• database_object: Specify the database object such as database table name, schema name etc. on which the permissions have to be granted.
• role: By role here, we mean a user login, group, or principal to which the said permissions have to be granted.
•  [WITH GRANT OPTION]: This option indicated that the said user role can further grant any of the above-mentioned permission to some other user. Basically delegating or sharing his permissions with someone else.

REVOKE Permission Syntax

REVOKE<Permission>
ON database_object
TO role
CASCADE;

The parameters used in the above-mentioned syntax are very similar to the ones mentioned in the GRANT syntax.

DENY Permission Syntax

DENY <Permissions>
ON database_object
TO role
CASCADE;

The CASCADE keyword in the last two syntaxes indicates that permissions will be taken back from other users to whom this user has granted permissions.

Examples of SQL Server Permission

To illustrate a few examples of SQL Server permissions, we need to create a dummy user. Let’s call it, “user_1”. A user can be created in the following manner.

Step 1: Move to the Security section of the SQL server and right-click on Logins.

Step 2: Create a new login by filling all the required details in the dialog box.

Step 3: Once you are done creating a login, do not forget to map the new user, using “User Mapping”.

And we are all set to grant, revoke and deny permissions to this user.

Example #1

SQL query to grant select permission to a user.

GRANT SELECT ON [master].[dbo].[Account_details] TO user_1
WITH GRANT OPTION;

Example #2

SQL query to revoke select permission from a user.

REVOKE SELECT ON [master].[dbo].[Account_details] TO user_1;

We got an error. Yeah true because when we granted this user SELECT permission, we granted it with [WITH GRANT OPTION]. So, yeah we have to revoke that too.

REVOKE SELECT ON [master].[dbo].[Account_details] TO user_1
CASCADE;

Example #3

SQL query to grant ALTER permission to a user.

GRANT ALTER ON [master].[dbo].[Account_details] TO user_1;

Example #4

SQL query to deny ALTER permission to a user.

DENY ALTER ON [master].[dbo].[Account_details] TO user_1;

Recommended Articles

This is a guide to SQL Server Permission. Here we also discuss syntax and parameters of sql server permission along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. SQL DATEPART()
2. MySQL Offset
3. SQL Window Functions
4. SQL Users