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.
October 5, 2022 at 4:03 pm
First I would think would be to try this:
GRANT SELECT ON dbo.view_name TO [domain\account];
GRANT SELECT ON dbo.view_name2 ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 5, 2022 at 4:18 pm
Good point I should have been more clear.
The service account has SELECT permissions granted to the views in question.
As ownership chaining only works within a database, with a view you will either have to create domain\MyServiceAccount as a user in the Finance database with SELECT permission on the tables or use Impersonation, TRUSTWORTHY, or Cross-DB Ownership Chaining. Please be aware of the following:
If you use a SP, or the anti-pattern of a multi-statement TVF, then module signing can be used. This is mentioned in the above link but there is more detail here:
https://www.sommarskog.se/grantperm.html
October 5, 2022 at 8:49 pm
Thank you, that is most helpful.
I was really hoping for a "magic" work around, and I wanted to be "lazy" and avoid module signing.
HOWEVER, as you said, that really is the best practice. I need to suck it up and do that!
Thank you for your help!
October 11, 2022 at 12:46 am
"Granting access to the base tables in the Finance database is not an option."
I had a similar problem with one of my databases and I just created a view in the target database and only gave the login select rights on that. If they query the view they can see the data but if they try and query the tables underlying the view, or any other tables, they get a select permissions denied error.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply