SQL Server 2012 - Select on View but not to underlying table in a different database

LeLeBrown
LeLeBrown used Ask the Experts™
on
On SQL Server 2012, I have two different databases(DB1 and DB2). I have created views on DB2 that select on tables from DB1. I have created a user on both databases that I want to be able to select on the views in DB2 but not select on the underlying tables on DB1. When I select on the view as the new user, I get a error saying I can't get access to the underlying table. The new user has connect access to DB1 and select on the views on DB2 (has select under Securables). Nothing else is set (under owned Schemas or Membership). Both databases have the same owner (SID same on both). I have tried doing the alter database DB1 set DB_Chaining on and granting the user select and/or connect but I still get the error.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
Do those tables need to be in the separate database really?
Or do the view's truly need to select cross databases? If you really need to do this (or just go cross schema) the user needs access to the underlying tables so better create separate views in each DB if needed.
If all you need is to grant a user(or role for that matter) access t a view but not underlying tables all you need is to grant SELECT on  the view but not the underlying tables and this will allow the user to query the view but not the tables. All these under assumption that all objects are  in the same database  - you could try use sample code below for testing.


create login test with password = '123pass'
go
create user test for login test
go
create table t1 (id1 int, col1 nvarchar(10))
go
create table t2 (id2 int, col2 nvarchar(10))
go
create view vt1t2 as select t2.id2, t1.col1, t2.col2 from t2 inner join t1 on t2.id2 = t1.id1
go
grant select on vt1t2 to test
go

Open in new window

Author

Commented:
Thanks Icohan. I was able to give the appropriate access in the same database with no problem. However, the requirement I was given was to set up two separate databases. We have another server where this has been done and I have compared the user settings and they are the same but it still doesn't work.
G Trurab KhanSnr. Development Manager

Commented:
Who own these views. Mqke sure that owner of vies has the rights to select underlying tables.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
The views are under the schema owned by dbo. Both databases are owned by the same user though.

Author

Commented:
I was able to get this to work by changing the owner on both databases to sa and db_chaining set to on for both. I am not sure why that worked since both databases already had the same owner.  I had already had db_chaining on. The owner was a user. Maybe that had something to do with it.
Database Analyst
Commented:
Obviously "sa" would have all the rights in SQL as it is the "God" account right? However in my opinion that is not 100% what you asked for in the description: " I have created a user on both databases that I want to be able to select on the views in DB2 but not select on the underlying tables on DB1."  however if  this solves your problem it is all that matters.

Author

Commented:
Both databases were created by users in the admin group before this. I am not sure if that had anything to do with it.

Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial