Forum Discussion
Restricting Access to Underlying Tables in Different Databases While Allowing View Access
Hi Experts,
I am looking to get assistance with the concept of ownership chaining in the SQL Server Database 2016. We have a data warehouse database that is maintained by an ETL Tool. We have segregated objects (e.g. Views, store procedure) from the data warehouse database to the another database on the same server. The non-data warehouse database which references to the Data warehouse database has very complex model of permissions and roles. I am unable to grant access to the objects mainly views in the non data warehouse database without granting select/View definition permissions on the data warehouse database.
I'd like to know if it is possible to grant access to the views in the non-data warehouse database without granting direct access on the actual objects like Tables in the data warehouse database. Using windows Microsoft SQL Server 2019 (RTM-CU22-GDR)
1 Reply
Hi, the short answer to your question is no.
The long answer is:
The moment you write a cross-database view or procedure, the permissions required to use that item become far more complicated than read-write for just that database.
You will have to determine the lowest level of access that can be granted to enable a user to pull from a cross-database view, or call a cross-database stored procedure.
Some objects will allow you to view dependencies - that might help, but it will only tell you what objects it references - it won't tell you whether the account needs read or write permission to those objects. In terms of a stored procedure, you will have to determine that, based upon your understanding of the procedure.
The neater solution would be to use a replication tool to replicate the data you want into your main data warehouse database. This would eliminate the need to consider cross-database permissions or roles.
