none
SOLVED : SQL Server 2008R2. Cross-database VIEW. Grant SELECT to user on one database only. RRS feed

  • Discussione generale

  • Dear all,

    I'm working with SQL Server 2008R2.

    I created two database: 'db1' and 'db2' with the same owner 'User3'. All tables in these databases have the same schema name 'dbo'.

    'db1' contains a huge table: 'db1'.dbo.'sales'
    This table contains all my sale details.

    'db2' contains only a view: 'db2'.dbo.'sales_filtered_view'
    This view select from 'db1'.dbo.'sales'  just few rows  according to security filters. In example: 

    SELECT * FROM DB1.DBO.SALES WHERE COUNTRY = 'IT';

    I created a user. I don't want to grant the user with the permission to access 'db1' and read directly from 'sales' table. On the other hand, I want to grant him the access to 'sales_filtered_view'.

    Obviously, if I just appy permission as described, I get this error message:

    The server principal "UserLimited" is not able to access the database "db1" under the current security context.

    I'm sure this is not the correct way to configure the grants.

    May you show me which is the correct MSSQL way to managed a situation like this?

    Thanks in advance.

    Fabio Landi

     

    -------- Change on post -------
    -------- ADDED on 2011-11-10 at 14.35

    Going on looking in MSSQL documentation, I found this article:

    http://msdn.microsoft.com/en-us/library/aa196729(v=SQL.80).aspx

    I tried to enable Ownership chaining using:

    USE master
    EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
    EXEC sp_dboption 'db1', 'db chaining', 'ON'
    EXEC sp_dboption 'db2', 'db chaining', 'ON'

    But it doesn't work yet.

    Any idea?

    -------- Change on post -------
    -------- SOLUTION on 2011-11-10 at 14.35

    Looking around internet I found this useful post.

    http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/

    Following these steps I have been able to implment my cross-database VIEW.

    My error was: 'UserLimited' must have access to 'db1'. There is NO need to enable 'db_datareader' role: the user cannot read any row even if he has access to 'db1'

    I hope this helps.

    • Modificato Landif giovedì 10 novembre 2011 15:36
    • Tipo modificato Anca Popa venerdì 11 novembre 2011 14:19 il thread e' stato gia' risolto
    giovedì 10 novembre 2011 12:10

Tutte le risposte

  • Ciao Landif,

    Ti ringrazio di aver aggiornato il thread per condividere la soluzione con la community. Questo Forum fa parte della gerarchia italiana, per il futuro sei pregato di postare in italiano, se vuoi usare l'inglese questo indirizzo e' piu' appropriato: SQL Server Forums.

    Saluti,


    Anca Popa Follow ForumTechNetIt on Twitter

    Evento 36888 origine Schannel su Windows Server 2008 R2 con IIS

    Microsoft offre questo servizio gratuitamente, per aiutare gli utenti e aumentare il database dei prodotti e delle tecnologie. Il contenuto viene fornito “così come è” e non comporta alcuna responsabilità da parte dell'azienda

    venerdì 11 novembre 2011 14:19