security for db pointing on snapshot

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

 

 

Answer 1

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

Answer 2

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

 

 

 

 

 

Answer 3

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

Answer 4

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/

Answer 5

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/

Answer 6

deny select  did not work