question

FedericoCoppola-2569 avatar image
0 Votes"
FedericoCoppola-2569 asked Cathyji-msft commented

SQL read only user for specific tables

Hi all,
I am not a Microsoft SQL expert, but I need to solve this trouble.

I must configure a MS SQL access as read only user.
I understood that I must set "db_datareader* permission for this new read only user (guide: https://www.itsupportguides.com/knowledge-base/server-side-tips/sql-management-studio-how-to-create-read-only-users/)

After that I would limit read only user database view.
I would that this limited user can only see specific tables inside specific database (databases).
Is it possible?
How can I do it?

Thanks
Best regards

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @FedericoCoppola-2569,

We have not received a response from you. Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

Hi @FedericoCoppola-2569 ,

Any update for this thread? Did the reply(s) could help you?

0 Votes 0 ·
pituach avatar image
1 Vote"
pituach answered pituach edited

Good day and welcome to the QnA forums :-)

I would that this limited user can only see specific tables inside specific database (databases).

In this case you do not need to give permission in the database level but only GRANT SELECT on the specific tables

For example:

 GRANT SELECT ON OBJECT::Person.Address TO RosaQdM;  
 GO

You can get more information in the following document

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver15

Note! "create read only users" ! You create a user and a user has no permission. If you did not give the user any permission then he cannot do anything. You grant permissions to SELECT in order to "read" and again, if you did not give him other permission then he can only read

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered Cathyji-msft edited

Hi @FedericoCoppola-2569,

Agree with pituach. I did a test in my environment.

The important thing is to not give the user 'db_datareader' access to the whole database. Using the UI you can use the User Mapping tab under the login, you can create the user with 'public' access only. Then you can go to the database and grant that user SELECT access to the particular table.

Run below T-SQL to limit access to only one table (tableA).

 use test
    
 GRANT SELECT ON [dbo].[tableA] to [Cathy2]
 Go

141210-screenshot-2021-10-18-155255.jpg141265-screenshot-2021-10-18-155239.jpg

141180-screenshot-2021-10-18-152208.jpg141259-screenshot-2021-10-18-152230.jpg

This is an old similar thread SQL Server : can you limit access to only one table. Hope it could help you.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.