1

there is a sql query that joins both example1.dbo.table1 and example2.dbo.table2

User1 has no select permission on example1 DB.

User1 has select permission on example2 DB.

User2 can run that query

how can User1 run that query ? I dont want to give select permission to User1.

Is there any possibility of that ?

I created this query as procedure and added "execute as user2" inside of it but still couldn't run for User1.

1 Answer 1

3

You will need cross-database permissions.

The most secure solution is to create a dedicated user from Certificate in the "example1" DB and grant this user read privileges. Then in the "example2" DB you will sign this stored procedure with the same certificate, so the procedure will be able to use permissions of the previously created user.

You can find detailed tutorial here. And for a longer elaboration on the topic, check out Erland's classic article.

Another option would be to enable 'Cross-database ownership chaining', but it is not recommended because of security implications.

1
  • Is this works on azure sql server and databases?
    – bestpro
    Commented Oct 1, 2020 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.