I have two databases on the same server. I have a service account I want to have access to SELECT off some view in the "Staging" database. The view calls on tables in our Finance database. If I run the SELECT as myself or other "normal" users it works fine because those accounts have access to the base tables in the other database.
The tables in the Finance database are owned by the FinaceUser account (it's a vendor system and the AD account created for it to run under, and it created the tables in the database when the software was installed and ran its create database scripts).
Is there anyway around this other than creating a schema in my Staging database that is also owned by the FinaceUser account? What options do I have to resolve this?
Granting access to the base tables in the Finance database is not an option. I could just make a stored procedure to call instead and wrap it with EXECUTE AS but I really would prefer to use views for this.
Thank you for your help!
EDIT: The service account has been granted SELECT permissions on the views in the Staging database.
Presently, I get the expected error
Msg 916, Level 14, State 1, Line 2
The server principal "domain\MyServiceAccount" is not able to access the database "Finance" under the current security context.
- This topic was modified 2 months ago by Maxer.