Grant Read Access to Many Databases in Sql Server

In our earlier article, nosotros had learned how to create a login in SQL Server with help of GUI as well every bit T-SQL script.

If yous wish to go through that article the following is the link:

• Logins and Users in SQL Server

Granting read, write, execute, create, etc. in SQL Server comes under a security context, and beingness a Database Administrator, information technology's very of import to make sure that a user must accept sufficient permission to access the database and its objects once he/she gets a new login for the server.

The Database Ambassador must ensure that no other anonymous user can admission the database to perform whatever unauthorized activeness.

By default, the SQL Server denies access to database objects after you create a login for a user. Therefore, if you want to access the database objects with your login, you lot must have sufficient permissions provided by your database administrator.

Before proceeding, below are the details nosotros've provided for the demo:

• Database: CSharpCornerDB
• User: MChand
• Connexion Required: two (Admin Session, SQL Login Session)

Currently, we've connected with Admin login and have three tables with some information in it.

You can find fastened script to create database and objects used in this demo.

If you lot want to grant permissions to any user, y'all must be a member of sysadmin, db_owner or the SA account.

Allow's brainstorm with the demo.

Connect to your server session with the SQL login as in the following:

Subsequently successful authentication, you lot'll go continued to the server with the username as in the post-obit:

As we mapped our user to CSharpCornerDB database, we'll connect to it. But if you try to connect to other database, you lot'll end up with the following error message.

Msg 916, Level xiv, Land 1, Line 1
The server master "MChand" is non able to access the database "SQL432DB" nether the current security context.

As well if you lot attempt to expand other database node, SQL Server throws the following error bulletin window. And the '+' sign disappears as shown below.

So, this simply means that the user can only connect to the database on which information technology is mapped. We'll select our mapped database i.e. CSharpCornerDB.

Equally we already know, we accept three tables in our database, equally mentioned earlier. But if you expand the Tables folder you won't find whatsoever table in it. You'll only go the following output:

Also, if you try to execute the post-obit query to get data in tables, you'll get the following fault message.

1. SELECT  * FROM  tblArticles
2. SELECT  * FROM  tblCategories
3. SELECT  * FROM  tblAuthors

Error Message:

Msg 229, Level 14, Country 5, Line i

The SELECT permission was denied on the object 'tblArticles', database 'CSharpCornerDB', schema 'dbo'.

Msg 229, Level fourteen, State five, Line two

The SELECT permission was denied on the object 'tblCategories', database 'CSharpCornerDB', schema 'dbo'.

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

This is nada new but the default behavior of SQL Server to deny access on database objects later creating new login for 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, connectthe server with your admin account or with Windows account and follow the below steps.

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

You'll get Login Properties window every bit in the following screenshot:

Go to User Mapping tab and select the database on which y'all want to give permission and at lesser select db_datareader as shown beneath.

Click Ok and you lot're washed.

The following is the T-SQL for the aforementioned.

1. USE [CSharpCornerDB]
2. GO
3. EXEC  sp_addrolemember N 'db_datareader' , Northward 'MChand'
4. GO

Now go to the session on which you're continued with MChand login and refresh the Tables folder. If y'all see all the tables are now visible and are listed below.

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

1. USE CSharpCornerDB
2. GO
3. SELECT  * FROM  tblArticles
4. SELECT  * FROM  tblCategories
5. SELECT  * FROM  tblAuthors

Too, if you run SELECT command to become table details, yous'll get output for both.

Now let's insert another record into table tblArticles.

1. USE CSharpCornerDB
2. Become
3. INSERT INTO  tblArticles values ( 'Strings in C#' , 'Abhishek' )
4. GO

If you execute the above query, you'll see the following error bulletin:

Msg 229, Level fourteen, Land 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 database to the user, follow beneath steps.

Security -Logins, so double click on Login and you lot'll go Login Properties window.

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

Under 'Database function membership for' section, check 'db_datawriter' checkbox as shown below.

Click Ok and you lot're done.

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

1. Utilise [CSharpCornerDB]
2. Become
3. EXEC  sp_addrolemember Northward 'db_datawriter' , N 'MChand'
4. GO

Now if you execute INSERT command from MChand session, information technology'll add the tape without any mistake.

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

If y'all want to revert back the action you lot tin can execute the following query from admin session.

1. USE [CSharpCornerDB]
2. Go
3. EXEC  sp_droprolemember N 'db_datareader' , North 'MChand'
4. GO
5. USE [CSharpCornerDB]
6. Become
7. EXEC  sp_droprolemember Northward 'db_datawriter' , Due north 'MChand'
8. Go

Conclusion

So, this is an article based on SQL Security where we learned how to provide read/write permission for user to read and write data from/to SQL Server database.

Hope this article helps y'all o understand how to provide read write permissions. At that place are besides other permissions which you tin try with database on your server. If you need any help in this, please permit 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 and so keep learning and proceed sharing.

matthewsgotin1978.blogspot.com

Source: https://www.c-sharpcorner.com/UploadFile/ff0d0f/grant-read-write-permission-to-user-in-sql-server/

Share this post