Cross DB Ownership Chaining

Question

Cross DB Ownership Chaining

RonMexico

Hall of Fame

Points: 3474
More actions
January 12, 2017 at 8:26 am

#401366

I've been reading a bunch of articles about how to grant users permission to a view in one database that selects from a table in a separate database without actually having to grant them select permission on the table. I don't want to have to enable cross db ownership chaining so I'm checking to see if that is my only option before I move on from this topic. Here is the outline of what I'm trying to do.
I have a group of users set up in a role that has select permissions on View1 in Database2. View1 selects a filtered criteria from Table1 in Database1. I do not want the users in Database2 to have direct access to Table1 in Database1 -- only through the view. So select * from Database2..View1 should work but select * from Database1..Table1 should not. Any suggestions?

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

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

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

RonMexico - Thursday, January 12, 2017 8:26 AM
I've been reading a bunch of articles about how to grant users permission to a view in one database that selects from a table in a separate database without actually having to grant them select permission on the table. I don't want to have to enable cross db ownership chaining so I'm checking to see if that is my only option before I move on from this topic. Here is the outline of what I'm trying to do.
I have a group of users set up in a role that has select permissions on View1 in Database2. View1 selects a filtered criteria from Table1 in Database1. I do not want the users in Database2 to have direct access to Table1 in Database1 -- only through the view. So select * from Database2..View1 should work but select * from Database1..Table1 should not. Any suggestions?

Cross DB chaining would have to be turned on for both databases (you could turn it on for the server, but this is generally not recommended).
Unless you've explicitly set owners for Table1 and View1, you need to check who the owners of for the schema those two objects reside in. SQL Server, by default, doesn't assign an owner to the objects contained in a schema such as a table or view. If there isn't an owner at that level, it will use the owner at the schema level to determine ownership chaining. This is true for cross database ownership chaining as well. If the owner of the schema is dbo, then you need to see who owns the database according to sys.databases. Otherwise, you need to tie the users back to the logins that map into the database.
If the owners are the same, then cross database ownership chaining can take effect.
The users who need to query the view will need connect permissions into the database with the table (basically, added as a user). They don't need any other permissions. However, if they can't enter the database with the table in it, then a query across will fail.
Grant permissions against the view. No permissions should be granted against the table.

This should accomplish what you want.

K. Brian Kelley
@kbriankelley

RonMexico Hall of Fame Points: 3474 More actions · Answer 2

Thanks, Brian! That's some really good information and will help me accomplish what I'm trying to do. I'll mess around with it and get back with any questions I have.