Grant Read Write Permission To User in SQL Server

Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'tblAuthors', database 'CSharpCornerDB', schema 'dbo'.

This is nothing new but the default behavior of SQL Server is to deny access to database objects after creating new login for the user. If we want to access those objects, we require read permission for the user with which we’re connected.

To provide READ permission to the user on the database, connect the server with your admin account or with a Windows account and follow the below steps.

Expand Security - Logins, then Right Click on login and then click Properties.

You’ll get the Login Properties window as in the following screenshot:

Go to the User Mapping tab and select the database on which you want to give permission and at the bottom select db_datareader as shown below.

Click Ok and you’re done.

The following is the T-SQL for the same.

USE [CSharpCornerDB]  
GO  
EXEC sp_addrolemember N'db_datareader', N'MChand'  
GO  

Now if you execute the following select query, you’ll be able to see all the data in the table.

USE CSharpCornerDB  
GO  
SELECT * FROM tblArticles  
SELECT * FROM tblCategories  
SELECT * FROM tblAuthors  

Also, if you run the SELECT command to get table details, you’ll get output for both.

Now let’s insert another record into table tblArticles.

USE CSharpCornerDB  
GO  
INSERT INTO tblArticles values('Strings in C#','Abhishek')  
GO  

If you execute the above query, you’ll encounter the following error message:

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'tblArticles', database 'CSharpCornerDB', schema 'dbo'.

The error message simply states that INSERT permission is denied on the object. This means we don’t have permission to write any data in the database objects.

To give WRITE permission on the database to the user, follow the below steps.

Security - Logins, then double click on Login and you’ll get the Login Properties window.

Go to the User Mapping Tab and select the database on which you want to give permission.

Under the ‘Database role membership for’ section, check the ‘db_datawriter’ checkbox as shown below.

Click Ok and you’re done.

The following is the T-SQL script for the same.

USE [CSharpCornerDB]  
GO  
EXEC sp_addrolemember N'db_datawriter', N'MChand'  
GO

Now if you execute the INSERT command from MChand session, it’ll add the record without any error.

After getting WRITE permission, you can run INSERT, UPDATE, and DELETE commands.

If you want to revert back the action you can execute the following query from the admin session.

USE [CSharpCornerDB]  
GO  
EXEC sp_droprolemember N'db_datareader', N'MChand'  
GO  
USE [CSharpCornerDB]  
GO  
EXEC sp_droprolemember N'db_datawriter', N'MChand'  
GO  

Conclusion

So, this is an article based on SQL Security where we learned how to provide read/write permission for users to read and write data from/to the SQL Server database. If you are new to SQL, please read What is SQL.

Hope this article helps you o understand how to provide read-write permissions. There are also other permissions that you can try with the database on your server. If you need any help with this, please let me know. I’ll try my best to solve those.

Please provide your valuable feedback and comments that will help me in providing a better article the next time. Till then keep learning and keep sharing.

Next: Grant and Revoke Command in SQL Server (c-sharpcorner.com)