Permissions Again....

[NewBean]

Here is the scenario:

Company A would want to share his data with Company B. He only wants to share a view from his database. Currently, Co. A gave a public access to Co. B.

Co.A doesn't want that Co.B to look through the logic of the stored procedures, the tables structures ...plain and simple...the system...obviously the reason why Co.A created a view in the first place.

What's the best implementation to use in order for Co.A to allow Co. B to READ the data on his view...and not allowing Co.B to check out every object on the database...except his view...

 

[tlbroadbent]

Here is an option in SQL 2000.

1) Create a user on Database_A for a Company B login. This could be new login or an existing login.
2) Assign the new user to the db_denydatareader and db_denydatawriter roles.
3) Grant the user Select permissions on the view.
4) Create a view in Database_B that simply selects the records from the view on Datbase_A.

Example: View created in Database_B
Select * From Database_A.dbo.vCompanyInfo

5) Create a user on Database_B for the company B login, if it doesn't exist already.
6) Grant the user select permissions on the view in Database_B.

The user will not be able to select from the view on Database_A - only from the view on Database_B. The user will not even be able to see any objects on Database_A. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.