Posted by Erland Sommarskog on 10/01/56 11:17

Anon (anon email) writes:
> Sorry, I didn't clarify the view; it is created using "select * from
> dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
> dbo.BOOK attempts to run the query they get an error that states
>
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'BOOK', database 'LIBRARY', owner
> 'dbo'.
>
> What we'd like to see is the explicit rights on the View supercede the
> rights on the table, but that doesn't seem to be the case.

I will have to admit that if you granted Dept1 rights on dbo.Book, and
then the users rights to Dept1.book it would work, but nope. In fact
I even tried creating a stored procedure Dept1.book_sp and grant users
execute rights on that one, but that also failed. However, this latter
arrangeent actually works on SQL 6.5, so at least I did remember
correctly so far. (But Microsoft has changed the rules. Grr!)

Right now, I have to good ideas to get this to work in SQL 2000. In
SQL 2005, it would be another matter, because Dept1 would just be a
schema, that still could be owned by dbo.

Of course, you can create the view as dbo.Dept1books, but I don't
if that meets your ambition to fool the shrink-wrap package.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]