Creating a view which access objects in another database (without giving permissions to underlying base tables)

• HookSQLDBA7

  SSChampion

  Points: 10709

  More actions

  September 10, 2014 at 9:02 am

  #311484

  We are running SQL Server 2012 on Windows 2008 Server. In one database, we would like to create a view which access objects in another database without giving the user permissions to the underlying base tables in the other database. The ownership chain is broken in this case. Can this be accomplished (considering the ownership chain is broken)? If so, what is the easiest method to accomplish this task? Or

  Example 1 (Works):

  In DB1:

  --UserA selects from Schema1.View1 (which access tables in DB2).

  In DB2:

  --UserA exists with select permissions on the base tables accessed by Schema1.View1 (in DB1).

  Example 2 (trying to accomplish):

  In DB1:

  --UserA selects from Schema1.View1 (which access tables in DB2).

  In DB2:

  --UserA exists (or may not exists) with NO permissions on the base tables accessed by Schema1.View1 (in DB1).

  Thanks in advance, Kevin

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  September 10, 2014 at 9:08 am

  #1744458

  Not sure whether cross database ownership chaining[/url] would help here? Make sure you understand the risks before you do it.

  John