Security on Views
-
Hi,
I have created a view on a Database (DB2) that references a subset of a table on another Database (DB2). However when I run a query with a locked down user on DB2 who only has select access to the view on DB2 I get the following:
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Table', database 'DB1', owner 'dbo'.
Server: Msg 229, Level 14, State 1, Line 1
SELECT permission denied on object 'View', database 'DB2', owner 'dbo'.
I don't wish to grant this user access to DB1 at all. Surely the view doesn't require the user to have access to the base table? That would defeat the purpose of the view.
Any comments or suggestions would be very much appreciated.
Shawn.
-
"... I don't wish to grant this user access to DB1 at all ..."
They'll need access to the database to retrieve data. You don't need to apply any specific permissions to the user in DB1, just grant them access to the database. You'll probably also need to enable "Cross-database ownership chaining". This is done on the security tab of the Server Properties dialog, or the options tab of the Database Properties dialog.
Check this article for details on cross-database ownership chaining, http://support.microsoft.com/default.aspx?scid=kb;en-us;810474
For the second "permission denied" error, you will need to grant appropriate permissions on the view.
--------------------
Colt 45 - the original point and click interface
-
Thanks Phill. The "Cross-Database ownership chaining" was what I needed. Its working well.
Also with regards to the user access issue - you don't need to grant a user access to the base database at all, in this case DB1. Just to the views in DB2. The database chaining handles it all. I just confirmed this in testing. Just thought you might be interested.
Once again thanks.
Shawn
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply