I have a requirement to access (read) a table/view in database B from database A. The application that needs to execute the query however uses an Application Role. From what I’ve read and tried, doing Module Signing is not an option.
What would be the next best approach?
Is that setting TRUSTWORTHY on for database A and use EXECUTE AS?
Just found the answer:
- Create SQL login with minimal privileges
- Execute function from source database under that user (WITH EXECUTE AS)
- Module sign object
- GRANT AUTHENTICATE to user created from certificate (this is the one I missed)