Take the 2-minute tour ×
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.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.