RE: Need Help with Views – SQLServerCentral

Hall of Fame

Points: 3455

February 12, 2019 at 12:44 am

#2021749

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

I created schema and synonym in the destination database B

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

I created a schema C and TestSynonym in the destination database B. I had created views in database B referring to the schema

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM

Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?

Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

As per your direction I got it working

what i tried

I created a schema TestSchema and TestSynonym in the destination database B. I had created view ViewA in database B referring to the schema TestSchema pointing to the table A in database A.
I had granted select permission on TestSynonym to the User A in database. After that i tried to grant select permissions on TableA in database A.

After that i tried to run the run the view ViewA in database B. It works.

But if I am giving read access directly on TableA in database A. I don't need Synonyms in Database B to create and let the user A use it in Database B. The read permissions on TableA in database A will itself be sufficient to run the viewA or directly read from the tableA.

Database A is very regulated database. Users should not have direct access to it. Indirect access is okay as long thet can't see the tables directly in DatabaseA

When i gave select permissions to the user A on TableA in database A. The user is directly able to see the TableA in database A. If he can see that he can query it directly.That is against the requirement.