Post reply

Views in one database that reference data from a different database, without allowing access to the referenced database

  • December 3, 2015 at 3:07 am

    #322948

    Looking for help on the following. I think I know how it should work, but it isn't. So, let's ask the question of how to do this without murkying it up with what I've tried.

    Issue: How to allow users to use views in one database that reference data from a different database, without allowing access to the referenced database.

    Detail: Consider this simple scenario. Two databases (dbData and dbViews) on the same server (SQL Server 2012). Once contains data (dbData), the other contains access to a subset of that data in the form of views (dbViews). We want to be able to give users read-only access to dbViews and no direct access to dbData. How do we set up permissions for user “Bernard”?

    Stuart

  • December 3, 2015 at 3:23 am

    #1843714

    Stuart

    You need to read about cross-database ownership chaining. Please post back if there's anything you still don't understand.

    John

  • December 3, 2015 at 10:18 am

    #1843841

    Well, that seems to be the key. One of my colleagues has done this previously (possibly on SQL 2005) very simply. We wonder if the server default for cross-database ownership chaining has changed between versions?

    Anyway, what we were doing was granting references permissions on the required objects, but it wasn't working. As John indicated, it was simply because the DB_CHAINING property was set to off. Turning this on has security implications (see MSDN), but we're comfortable with these.

    Code version of how this was achieved:

    --Turn cross-database ownership chaining on

    --referencing won't work without this property being set

    ALTER DATABASE [dbData] SET DB_CHAINING ON;

    ALTER DATABASE [dbViews] SET DB_CHAINING ON;

    --Create server login for bernard

    CREATE LOGIN [mydomain\bernard] FROM WINDOWS WITH DEFAULT_DATABASE=[dbViews]

    --Grant [db_datareader] role on [dbViews]

    USE [dbViews]

    CREATE USER [mydomain\bernard] FOR LOGIN [mydomain\bernard]

    ALTER ROLE [db_datareader] ADD MEMBER [mydomain\bernard]

    --Grant reference permission on an object in [dbViews]

    --(reference means that other objects (e.g. views)

    --can access the data but the user cannot execute a select statement)

    USE [dbData]

    CREATE USER [mydomain\bernard] FOR LOGIN [mydomain\bernard]

    GRANT REFERENCES ON [dbo].[MyTable] TO [mydomain\bernard]

    Thanks

    Stuart

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

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