locked
How to switch security context in a view RRS feed

  • Question

  • Hi

    I have 2 databases one for datawarehouse and one for reporting.

    I created a view in the reporting database joining several tables from datawahourse. The view is meant for a users who uses powerpivot. It will contain a few million of rows.

    I set up a role in the reporting database. I assigned select rights to that role on the view.

    When the  user wants to access the view, he can see the view but a select gives

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

    How can I fix this ? I don't want to give the user select rights on tables in the datawarehouse.

    Constantijn

    Friday, June 5, 2015 7:20 AM

Answers

  • Hello Constantijn,

    If all tables queried in the view then "ownership chaining" would take effect; the user only needs permissions for the view, but not for the base tables.

    But here view and tables are in different database, "cross database ownership" is disabled, so the user would need permissions for the second database + base tables, too.

    Why 2 databases and not just one?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 5, 2015 7:36 AM
    Answerer

All replies

  • Hmm, why not? if so read Erland's article 

    http://www.sommarskog.se/grantperm.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, June 5, 2015 7:31 AM
  • Hello Constantijn,

    If all tables queried in the view then "ownership chaining" would take effect; the user only needs permissions for the view, but not for the base tables.

    But here view and tables are in different database, "cross database ownership" is disabled, so the user would need permissions for the second database + base tables, too.

    Why 2 databases and not just one?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 5, 2015 7:36 AM
    Answerer
  • Thanks for pointing me in the right direction. I did the following

    EXEC sp_dboption 'datawarehouse', 'db_chaining', 'true';
    GO
    EXEC sp_dboption 'reporting', 'db_chaining', 'true';
    GO
    use datawarehouse
    GO
    EXEC sp_grantdbaccess 'pp_user';
    GO

    It works now but I dont understand why I had to set the db_chaining on the two databases and not one.

    Friday, June 5, 2015 8:45 AM