December 18, 2003 at 5:32 am
Hi!
I have views on one of databases that collect data from this database and few others.
These views filter data for columns and rows. I need few users with sql-login to see ONLY the data the views return and NOT to be able to see the rest of the data. What permissions should I set?
I guess for column filtering I just need to use by column permissions for tables on all databases and select permission on a view, but what about rows?
/* I am using MS SQL2000 sp3 + Win 2000 Adv Server 2000 sp4 */
Thanks.
Edited by - Roust_m on 12/18/2003 05:51:04 AM
December 18, 2003 at 6:59 am
If the view and the tables are in the same database, and they are all owned by the same user (such as dbo), you only have to apply permissions to the view. Ownership chaining will take effect meaning no explicit permissions have to be set on the base tables (in fact, this is often why we'd use views, to restrict the data a user sees).
If you cross databases, the rules change. If you're pre SQL Server 2000 SP3 or you have cross-database ownership chaining turned on (it's recommended you keep this turned OFF unless you absolutely need it) and the owner of the objects in both databases is the same, you get the same effect. What SQL Server does is map the owner of the object back to the actual login and compares the logins to see if the owner is the same. If cross-database ownership chaining isn't on (this option only exists in SP3/3a), you'll have to set permissions manually on the base tables if they exist in a different database than the view.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply