Select permission on view that reads from table in another database

Question

Select permission on view that reads from table in another database

hanrahan_tim

Right there with Babe

Points: 717
More actions
December 15, 2011 at 12:51 pm

#248239

I have a view in my database that reads from a table in another database on the same SQL instance.
This causes a permission error and without going down the ownership chain route what is the best solution to this problem.
Should I simply create each user in the other database that houses the tables for my view and gratn select on the tables.
Or is there a way from my database to grant select on another database without creating users on it?
I have granted users select on the view I have, but this does not seem to be enough as they don't have perms on the underlying tables in the other database.
Just looking for the best practice, or elegant way to handle this.
Thanks,
Tim.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 1

I think that you're either going to have to use ownership chaining or grant select on the tables in the other database. If you could add the users to a domain group, assuming they are Windows accounts, that would make it easier to maintain than having to add all of the users to the other database.

hanrahan_tim Right there with Babe Points: 717 More actions · Answer 2

Chuck Hottle (12/15/2011)
I think that you're either going to have to use ownership chaining or grant select on the tables in the other database. If you could add the users to a domain group, assuming they are Windows accounts, that would make it easier to maintain than having to add all of the users to the other database.

Seeing as there are a small number of users and they are windows accounts I'm going to add them that way.

This raises another question for me though:

I do have a domain group that contains the users that should have access to my app. I was able to add the domain group in under Logins. But each user in this app also gets special database roles and so I need a users account for everyone at the database level. I can't seem to do this while pointing back to the login that contains the AD group. It will allow me to add one user but that's it.

This is an older legacy app and I'm trying to clean it up as much as possible but if Logins map one to one to users then maybe I should just create a login for everyone seeing as they each need a user as well.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Maybe you can go for a certificate based authentication where you create users from a certificate in database A and then create a SP in database B which executes a view which queries the tables in database A.

Import the same certificate in database B and add a signature to that SP from the imported certificate.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server