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 only takes a minute to sign up.
Sign up to join this community
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.
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.
