I created view in database1 based on tables in database2. I gave SELECT
permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account in database2. How can I solve this issue? I don't want to create an account in database2.
Sql-server – Access view based on table in another database without account in that other database
permissionsSecuritysql serversql-server-2008view
Related Question
- Sql-server – How to refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?)
- Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables
- SQL Server – Linked Server Login Accessing Non-Default Database
- SQL Server Permissions – Grant Select Access to a User in a View Without Table Access
- SQL Server – Cross-Database Permission Chaining for sa/dbo Owned Objects
- Azure SQL – Select Permission Denied on Object
Best Answer
This is easy to accomplish in a very secure way using Module Signing. This will be similar to the following two answers of mine, also here on DBA.StackExchange, that give examples of doing just this:
Stored procedure security with execute as, cross database queries, and module signing
Permissions in triggers when using cross database certificates
The difference for this particular question is that it deals with a View, and Views cannot be signed. So, You will need to change the View into a multi-statement Table-Valued Function (TVF) as those can be signed and can be accessed just like a View (well, for
SELECT
access).The following example code shows doing exactly what is being requested in the question in that the Login / User "RestrictedUser" only has access to "DatabaseA" and is yet able to get data from "DatabaseB". This works only by selecting from this one TVF, and only due to it being signed.
Accomplishing this type of cross-database access while still using a View, and not giving the User any additional permissions, would require enabling Cross-Database Ownership Chaining. That is far less secure because it is completely open-ended for all objects between both Databases (it cannot be restricted to certain objects and/or Users). Module Signing allows just this one TVF to have the cross-DB access (the User doesn't have the permission, the TVF does), and Users that cannot
SELECT
from the TVF have no access to "DatabaseB" at all.All of the steps above recreate the current situation: the User has access to DatabaseA, has permission to interact with an object in DatabaseA, but gets an error due to that object in DatabaseA accessing something in DatabaseB where the User does not have any access.
The steps below set up the Module Singing. It does the following:
SELECT
permission to the Table in DatabaseB to the Certificate-based UserModule Signing setup:
IF ACCESS NEEDS TO BE THROUGH A VIEW, for whatever reason, then you can simply create a View that selects from the TVF shown above. And, in that situation,
SELECT
access does not need to be granted to the TVF, only to the View, as demonstrated below:And now to test it:
For more info on Module Signing, please visit: https://ModuleSigning.Info/