Rights question on SQL Server 2008

Question

I've got a database 1 with a user A with data_reader right on it.

I've got a database 2 with a user B wuth data_reader right on it and no access to database 1.

I need to :

• create a view for B on 2 with a reference on table from 1.
• B does not have to see database 1 (In Management studio)
• B does not have to show the T SQL code for the view, and can only do a select on it.

Is it possible?

Answer 1

Yes, this is called "cross-database ownership chaining", and can be a real pain.

The trick is to make sure that the following is set up:

1. Both DB2.View and DB1.Table are owned by the same SQL user (obviously a user that has access to both databases, usually an admin user like SA).

2. "Cross-database ownership chaining" is enabled on the server, or on the databases.

See http://msdn.microsoft.com/en-us/library/ms188676.aspx for a more detailed discussion, and a neat diagram.