patternsqlMinor
Grant permissions on database except specific tables
Viewed 0 times
tablespermissionsgrantdatabasespecificexcept
Problem
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?
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?
Solution
Broadly speaking you can do this in two ways.
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.
- 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)
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.
Context
StackExchange Database Administrators Q#168228, answer score: 5
Revisions (0)
No revisions yet.