SQL Server : Execute as multiple users simultaneously?

Question

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.

Answer 1

No, you can't.

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

Answer 2

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.

Answer 3

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.