Proficient Way For Giving and Removing Permissions in SQL Server

Overview

While working with SQL Server Database, users has the right to assign or remove permission, to allow which user can perform particular tasks on the database. Due to security reasons, only some of the default database users such as db_owner, db_datawriter, etc. are granted access to the database. However, some database needs to be accessed by other users at times. For this purpose, some commands can be used to grant or deny access in SQL Server. Through this page, we will learn the technique for giving and removing permissions in SQL Server.

SQL Server Commands

There are many commands supported by SQL Server, however in this case we will define only the three commands that can be used to give and remove access to the SQL Server users. They are as follows:-

i. GRANT- It is used to give users permission to perform some tasks on the SQL Server Database objects.

ii. DENY- It is used to deny any access to an user from performing certain tasks on database objects.

iii. REVOKE- It is used to remove grant or deny permission from the user that was earlier assigned to perform any tasks on the SQL database objects.

Giving Permissions in SQL Server

As stated in the previous paragraph, for giving permissions we will use GRANT command. The following syntax will be used for granting privileges on a table in SQL Server database:-

GRANT privileges ON object TO user;

Privileges: Some privileges that can be assigned to the user are as follows:

1. SELECT Able to perform SELECT statements on table.
2. INSERT Able to use INSERT statements on table
3. UPDATE Able to use UPDATE statement on table
4. DELETE Ability to perform DELETE operation on the table
5. ALTERTo perform ALTER TABLE statements to modify table definition

Object: It denotes the name of the database object that needs to be given access. For example, if we want to grant access on a SQL Server table, object will be the table name.

User: It will define the name of the user to which we need to grant access over the object.

Some Examples:

• GRANT INSERT, UPDATE ON Students TO John, Jessica; [Here, the command will allow John and Jessica to insert or update data in table Students]
• GRANT SELECT ON Students TO public; [Here, we will grant only SELECT access on the Students Table to all users by giving access to public]

Removing Permissions in SQL Server

Similarly, for removing some or all permissions on a table in SQL Server database we will use REVOKE command on some or all the privileges. Privileges can be combination of SELECT, INSERT, UPDATE, DELETE or ALL. The following syntax will be used for revoking privileges on the SQL Server Table:

REVOKE privileges ON object FROM User;

Here, Privileges can be any of the privileges discussed earlier in GRANT section. Object will the name of the database object from where we are removing permissions. User will be the name of the users whose permissions that was already assigned will be removed.

Some Examples:

• REVOKE DELETE ON Students FROM Alex; [Here, User Alex has been removed permission to perform delete operation on Students table]
• REVOKE ALL on Students FROM Maria; [Here, it is used to remove ALL permissions i.e., SELECT, INSERT, UPDATE, DELETE & REFERENCES from Maria User in Students Table]

Denying Permissions in SQL Server

A DENY command can be used to deny the DELETE any access or privileges on a SQL Server database object.

Example: DENY UPDATE ON Students to Oliver; [Here, the statement will deny the UPDATE operation on Students Table by user Oliver.]

Conclusion

The content has been aimed to guide users of SQL Server database in giving or removing permissions in SQL Server database objects using some commands. Additionally, we can grant users permissions not only on objects, but also on other tasks like creating tables, views or stored procedures. Using these commands, we can give access to perform tasks on database by the desired users instead of using the default database roles.