CODEWITHSUNDEEP
sql-serverdatabasecross-database
Srimant
Srimant

Reputation: 61

Cross Database Security

I have 2 databases X&Y. I have a user on X which needs to access Table in Y through a view in X. X has a synonym built to access table on Y. I need the user to access the view,not access the synonym (i acheived this through DENY GRANT),not access the table in the Y database directly (GRANT permissions are not helping).Both the Databases are on same instance in SQL Server-2012. Is there any way out Linked Server on the same instance are not possible.

User->Database X->View->Synonym->Database Y->Should see Data User->Database Y->Table->Should not see Data

Upvotes: 1

Views: 138

Answers (1)

Srimant
Srimant

Reputation: 61

I ended up creating two views with the same name (V_W) in both the databases.I wrote the logic for fetching my required set of data from X in the view created in Y.I created the view in X with a simple call to view in Y and it worked.Not the best of the process at least i achieved my purpose

Upvotes: 1

Related Questions