Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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?

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.