I've just started out with MS-SQL, so be patience with me :) I have 3 databases and I need to grant read access to user on all 3 databases, the challenge for me is how I can give him the permissions except for specific tables?


Broadly speaking you can do this in two ways.

  1. Grant read privileges on only the tables you want them to have access to. Or,
  2. Grant them access to all tables and then Deny access on the tables you dont want them to have access to.

The DBA giveth... and the DBA taketh away

There are lots of articles and references available that explain how they work together such as this one.

The keys words to search for are "sql server grant & deny". Permissions can be granted on the whole database, a schema or a specific object. (Table, view, stored proc etc)

This can be applied per user or via a Role. Rather than modify the existing roles, you could create a new role and grant the permissions you want to the role, and the give users that.

The most suitable method depends on how many tables they should/shouldnt have access to and how many users you would have to repeat this for.

If there is 100 tables in the db, and the user should read 90 of them, id grant + deny.

If you had to do the same thing for 5/10/50 users i'd create a role.

| improve this answer | |

You can look at how to add users to Roles :How to: Add a User to a Role

run this query on all the databases ,where you want to give access to the user

EXEC sp_addrolemember N'db_datareader', N'UserName' 

now to deny access on specific tables,see this :DENY Object Permissions

DENY SELECT ON OBJECT::tablename TO username; 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.