At the risk of posting yet another
message on cross chaining... I'm at my wits end. I've read and read until my eyes hurt. I'm missing something. All I want to do is create a view in database B that accesses an table in database A, that I don't want the user to ever access directly.
Here's the story: I have a giant database called "DataMart". In that database is a table innocuously called "Branch". According to the Auditors, the branch table contains data that will cause some people to go blind. Some of those people are my friends, and I would hate for that to happen (to the friends list).
I have a 2nd database called "MiniMart" where I plan to park views that a) reduces the number of objects they can see and b) restricts (or mangles) columns they cannot see.
Here is what I have:
SQL Server 2k8
Cross DB Chaining at the server is OFF.
Cross DB Chaining in both databases is ON
Trustworthy is ON for both databases.
Both databases are owned by the same user (idAdmin)
SELECT is revoked for PUBLIC on DataMart.DBO.Branch
my view and the Branch table are bothed owned by DBO.
I have a test user called "LowAccessTest", who exists as a user in DataMart but has no specific privileges (If I give this user db_datareader, they can see everything in the datamart database).
LowAccessTest is db_datareader in MiniMart.
In MiniMart, I have created a view:
create view DBO.limited_access_test as
select branch_nbr, name from datamart.dbo.branch
grant select on limited_access_test to LowAccessTest
I then make a connection to MiniMart using LowAccessTest and attempt to query limited_access_test and I get:
Error: The SELECT permission was denied on the object 'BRANCH', database 'DataMart, schema 'dbo'. (State:42000, Native Code: E5)
If I make LowAccessTest a db_datareader in DataMart, my view works, but I can also query datamart.dbo.branch.
If I turn of db_datareader and grant Select on datamart.dbo.branch to LowAccessTest my view works, but I can also query datamart.dbo.branch.
What have I missed?