SQL read only user for specific tables

Question

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

Answer 1

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://learn.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

Answer 2

Hi @Federico Coppola ,

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  

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.