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 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
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?