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.
- Grant read privileges on only the tables you want them to have access to. Or,
- 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)
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.
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;