SELECT permission on view querying table from another database

Question

SQL Server 2008 R2 SP1

My company uses the Great Plains (GP) financial system with several customizations. Our Value-Added-Reseller (VAR) for GP has setup most of these customizations in a separate database from the GP data called DYNCUSTOM.

In the DYNCUSTOM database is a View that is merely a SELECT * FROM a table in the GP company database (called PARTS). I was approached by a user having problems trying to select on this view.

The error he was getting:

Msg 229, Level 14, State 5, Line 2
SELECT permission denied on object 'BM010415', database 'PARTS', schema 'dbo'

I looked at how his login was mapped and he had a database user in the DYNCUSTOM database belonging to a database role that was granted select on the view.

However, he did not have a database user in the PARTS database.

Usually the intent of a view is so that one can grant select permissions on the view without exposing access to the underlying table. With SQL Server however, if the view crosses databases does it then change the security context being used? Therefore, the user would also need a database user in the PARTS database as well as granted select access to the underlying table?

Answer 1

Sounds to me like you have a case of ownership chaining. The link should provide you with the details on how to make sure the chain stays intact.

Answer 2

If you trust the database owners (which you should be able to if it's all just you), you should be able to turn on cross-database ownership chaining, which will allow security to chain in the way you want.

Essentially, if the same login owns the dbo schemes in both databases, CDOC will allow someone to query the view without explicif permissions on the table.