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

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.
LeLeBrownAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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

0
LeLeBrownAuthor 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.
0
G Trurab KhanSnr. Development ManagerCommented:
Who own these views. Mqke sure that owner of vies has the rights to select underlying tables.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

LeLeBrownAuthor Commented:
The views are under the schema owned by dbo. Both databases are owned by the same user though.
0
LeLeBrownAuthor 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.
0
lcohanDatabase AnalystCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeLeBrownAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.