principlesqlCritical
SQL Server - granting permissions to an entire schema vs. object?
Viewed 0 times
permissionsentiresqlgrantingserverobjectschema
Problem
I'm very green when it comes to the world of database permissions management in SQL Sever.
Let's keep an example simple.
Say account 'admin' is the owner of schemas A, B, and C.
There another account 'minion' that you want to have full rights (update/delete/insert/select/alter) on any object (table/view) created under schemas A, B, and C.
Is this possible? Or do you have to execute a grant statement each and every time you add a table/ view under these schemas? (seems a bit silly to me).
Let's keep an example simple.
Say account 'admin' is the owner of schemas A, B, and C.
There another account 'minion' that you want to have full rights (update/delete/insert/select/alter) on any object (table/view) created under schemas A, B, and C.
Is this possible? Or do you have to execute a grant statement each and every time you add a table/ view under these schemas? (seems a bit silly to me).
Solution
You can
Grant Schema Permissions
Further to that, if you want to then deny permissions on a certain object within that schema, you can do.
Denying Object Permissions
GRANT schema permissions that are effective for everything existing and everything that will exist in that schema.Grant Schema Permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: TO ;Further to that, if you want to then deny permissions on a certain object within that schema, you can do.
Denying Object Permissions
DENY INSERT ON OBJECT::. TO ;Code Snippets
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: <schema> TO <user>;DENY INSERT ON OBJECT::<schema>.<object> TO <user>;Context
StackExchange Database Administrators Q#125399, answer score: 72
Revisions (0)
No revisions yet.