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.