locked
security for db pointing on snapshot RRS feed

  • Question

  • Scenario (sql2005 Ent. Edition)

    On Server_A db Price set for data mirror to Server_B db Price. (db size = 300GB)

    On Server_B every 1 hr I generate snapshtot of db Price with name snap_Price

    On server_B I have another db Accounts with 2 views linked to table1 and table2 in snap_Price

    on server_B

    My action

    Created server login Mydomain\accounting on server_A and server_B with default db master

    Granted Mydomain\accounting public on master and read on price on server_A and public to master on server_B

    After snapshot generation of snap_Price on server_B I drop login Mydomain\accounting from server_A, so group Mydomain\accounting lost conenction to server_a ,but

    Still have access to server_B snap_Price

     

    What I want

    domain group Mydomain\accounting should have rights to select data from 2 views in Accounts db ,BUT do not have direct access to table1 and table2 to snap_Price on server_B

     

     


    Friday, April 8, 2011 6:49 PM

All replies

  • Until view and tables are belong to same schema,if account have permissions on view even though it does not have permissions on tables. Account should be able to see the data from views.
    http://uk.linkedin.com/in/ramjaddu
    Friday, April 8, 2011 7:12 PM
  • view linked to different db

    use Accounts

    create view vew_1

     select T1.*  from accounts.dbo.tablex as T1

     join snap_Price.dbo.table1 as t2 on T2.col1 =T1.col1

    user Mydomain\accounting  should be able to run

    select * from Accounts.dbo.view_1

    and SHOULD NOT

     select * from snap_Price.dbo.table1

     

     

     

     

     

    Friday, April 8, 2011 7:24 PM
  • NO - it won't work as objects are belongs to different databases, unless you grant select permissions on the table to public.
    http://uk.linkedin.com/in/ramjaddu
    Friday, April 8, 2011 8:44 PM
  • What if you DENY SELECT on that table

    DENY SELECT on TBL TO User


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, April 10, 2011 8:16 AM
  • What if you DENY SELECT on that table

    DENY SELECT on TBL TO User


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Monday, April 11, 2011 5:45 AM
    Sunday, April 10, 2011 8:16 AM
  • deny select  did not work
    Tuesday, April 12, 2011 9:01 PM