Error using view to limit data access between databases in SQL Server [duplicate]

Posted on

Question :

I am trying to limit user access to data through a view. I have a database full of replicated data that I do not want the users to be able to access, I also have a reporting database in which I will create views on the replicated data that the users can access.

e.g.
In database ReplData, I have a table refData(value int, source char(1)) with data values :
1, A
2, A
3, B
4, B

I want to use a view in a different database to limit users to just those rows with source = ‘A’

So, in database Reporting, I have a view

use Reporting 
go

create schema SourceA
go

create view SourceA.refData 
as 
  select * from ReplData.dbo.refData 
   where source = 'A'
go

create user Steve for login Steve
go

grant select on SourceA.refData to [Steve]
go

However, when user ‘Steve’ connects to Reporting and tries to select from the view, he gets an error

Msg 916, Level 14, State 1
The server principal 'Steve' is not able to access the database 'ReplData' under the current security context

What have I missed?

[edit] The problem is not a select permission, it is a security context. If you can identify what I have missed, please do. I have turned cross database chaining on (see comments) but it has not resolved my issue.

[edit 2]
So the issue was the method I was using to test the view, which was to ‘execute as user’. When I set up a new user and connected to SSMS using those credentials, then the view worked fine.

Answer :

A user must have a security context to access the ReplData database. That can be their own account, an AD group of which they are a member, or the guest user (if the guest user is enabled in the ReplData database).

No permission need be granted on the ReplData.dbo.refData table as long as the ownership chain is unbroken. Since the refData table is owned by dbo, the implication is the login that owns the ReplData database must be a database user in the Reporting database and that user must own the SourceA schema. Alternatively, the Reporting could be owned by the same login that owns the ReplData database and the SourceA schema owned by dbo.

You can change the schema or database owner with ALTER AUTHORIZATION, if needed:

ALTER AUTHORIZATION ON SCHEMA::SourceA TO <ReplData-database-owner-user>;
ALTER AUTHORIZATION ON DATABASE::Reporting TO <ReplData-database-owner-login>;

Also, the DB_CHAINING database option must be on in both the ReplData and Reporting databases.

In the case of stored procedures, you can get around the need for a user context by signing the module with a certificate that exists on both databases. That’s not an option with a view, though.

Leave a Reply

Your email address will not be published. Required fields are marked *