Menu
I want to restrict a user to only one schema and to only Select privilege in that schema in SQL Server 2008.
770
The U1 user has the CREATE VIEW permission on the database and the SELECT permission on the S1 schema. Therefore, the U1 user can create a view in the S1 schema to query data from the denied object T1, and then access the denied object T1 by using the view.
Only an authorization ID with ACCESSCTRL or SECADM can grant the following privileges on schema names starting with SYS: SELECTIN privilege on SYSCAT, SYSFUN, SYSSTAT or any schema names starting with SYSIBM (SQLSTATE 42501). SELECTIN, CREATEIN and DROPIN privileges on SYSPROC, SYSPUBLIC or SYSTOOLS schemas.
User-schema separation allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows you to group objects into separate namespaces.
You can restrict access to data at the following levels: You can use the GRANT and REVOKE statements to give or deny access to the database or to specific tables, and you can control the kinds of uses that people can make of the database.
A combination of DENYs and a GRANT. For example:
DENY SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
GRANT SELECT ON schema::[safe_schema] TO [user_name]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
