Grant Write Access To Table In SQL Server

As a database administrator working in an SQL server, one of my daily activities is to grant different users access to different database objects. In this article, I will walk you through multiple ways to grant write access to SQL server tables.

Check out How To Create A User In SQL Server Database before starting.

How To Grant Write Access To Table In SQL Server

Before discussing the methods, let me clarify: When given write access, the user can perform INSERT, UPDATE, and DELETE operations on a table.

Approach-1: Using Grant SQL statement

Syntax

USE DatabaseName;
GO

GRANT INSERT, UPDATE, DELETE ON dbo.tablename TO Username;

Example

Let us consider a real-time example in which I will grant Rajkishore write access to the MyProduct table in the Test database.

USE test;
GO

GRANT INSERT, UPDATE, DELETE ON dbo.MyProduct TO rajkishore;

The command was executed successfully after executing the above query, as shown in the screenshot below.

Check out How To Check User Permissions On Table In SQL Server

Approach-2 Using SQL Server Management Studio (SSMS)

Follow the below steps.

1. Connect to SQL Server Management Studio (SSMS).

2. Select your database and then expand the table node.

3. Right-click on the table name that you wish to provide access to and then select the Properties option, as shown in the screenshot below.

4. Click on the Permissions tab and then the Search button, as shown in the screenshot below.

5. Enter the user/role name and then click on the Check Names button; once it finds the exact user name, click on the ok button, as shown in the screenshot below.

6. Select the Grant column checkbox for Insert, Update, and Delete, and then, finally, click on the Ok button, as shown in the screenshot below.

Check out How To Check If CDC Is Enabled On A Table In SQL Server

Approach-3: By creating a Role

Here, we will create a database role first, later grant write permissions, and then add the user to that role.

1. First, create a role using the below SQL script.

CREATE ROLE ITTeam;

The command executed successfully, as shown below.

2. we need to grant write permissions to the above role using the below query. Where dbo.NewOrder is the table name.

GRANT INSERT, UPDATE, DELETE ON dbo.NewOrder TO ITTeam;

The command executed successfully, as shown in the screenshot below.

3. Finally, we need to add a user to the above role using the below query.

ALTER ROLE ITTeam ADD MEMBER rajkishore;

The query was executed successfully, as shown below.

Conclusion

Granting write access to tables in SQL Server is crucial when you see it from a data security point of view. As a SQL developer or DBA, it is important for you to know the best approach to do this. You can refer to the information mentioned in this article to achieve this.

You may also like following the articles below.

• SQL Server Create Table 2 Primary Key
• How To Get Table Count In SQL Server
• How to Export SQL Server Table to CSV