Tell me more ×
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.
up vote 1 down vote favorite

I need to query a proprietary database where the software vendor has set tight permissions on each schema (one username per schema), and each schema-user is only allowed to read from their own schema.

I now need to run a query across multiple schemas. Is it possible to run a single query against multiple user accounts? Something like EXEC AS USER 'A' AND 'B'?

Obviously the correct method is to create a user with all required permissions, but I am running the query from a stand-alone utility and will not have admin privileges on my future clients' instances.

share|improve this question
1  
If you need to read across multiple schemas then you need to work with the vendor to fix the permissions. Your needs and the structure of the database are incompatible – JNK♦ Feb 21 '12 at 15:20
@JNK Thank you for the comment, unfortunately I am not the immediate customer for the vendor but a third party developer... – ach Feb 21 '12 at 15:26
1  
Then there must be something that you can do. Potentially, if you have access to the multiple users needed for the schemas you need to query, you could query through a linked server and use different credentials, then save the intermediate results to a table in the other server, and combine after you poll them all. – JNK♦ Feb 21 '12 at 15:27

3 Answers

up vote 2 down vote accepted

No, you can't.

You could create user C that has permissions of A and B...

share|improve this answer
Unfortunately, I am not the database vendor, nor the customer, but a third party utility developer. Thank you for the response. – ach Feb 21 '12 at 15:28
It is a shame that there isn't another way, but your answer is correct. Thanks. – ach Feb 21 '12 at 18:51
up vote 1 down vote

Another suggestion that you can look into is SQL ownership chains.

With ownership chaining, a stored procedure can be created with permissions to the accessed objects. Then a user can be granted execute permission to the SP without needing explicit permission on the accessed objects.

share|improve this answer
Thank you for the suggestion. That is also a good link, and certainly useful in other situations. Unfortunately all of the current suggestions require existing higher-level access to the DB, which i don't have. – ach Feb 23 '12 at 22:16
up vote 0 down vote

You can use a different approach, namely code signing. Through code signing a procedure can be granted elevated permissions, in your case you can sign the procedure with a certificate and then create a certificate derived user and add this user to the db_owner role. This way the procedure gets de-facto dbo privileges. You would need a separate procedure for each action performed by your application.

Of course deploying these procedures, certificates, certificate derived users and signatures require elevated privileges to start with, but this is usually resolved by requiring the application setup to run in an elevated context.

share|improve this answer
Thanks for posting a second alternative. I think that in the end, since it appears I will have to ask the client to run to do some setup on their side anyway, I will create a separate user account. – ach Feb 21 '12 at 18:51

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.