How To Authorize Only a View Without Authorizing Tables (Ownership Chains)

 

If the view does not contain the object in another database, you only need to give the select authority on the view for the login without authorizing the tables. However, if another database is used in the content of the view, you will get an error as follows.

 

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object ‘xxx’, database ‘xxx’, schema ‘dbo’.

 

If you open DB_CHANING for the above two databases with the help of the following script, you will be able to select it without getting this error. These two databases must have the same owner.

 

Here is an important point to note:

Suppose you have a consolidated instance. So, a lot of applications are working on a single instance.

And an application is using 2 databases. You have to create a user who can select some views in these 2 databases.

You have created a view in database A and this view selects a table in the B database.

The user in which you give the select right to view in database A should not see the entire table (in database B) in the view (in database A).

In such a case, as I explained above, after making sure that the A and B databases are the same, the problem will be solved when you enable DB_CHANING with the help of the above script.

But in a consolidated environment if this need arises in another application, we will face other problems.

 

For example, a similar need occurred in databases C and D and we activated DB_CHANING in two databases as above. Therefore, DB_CHANING has been activated in databases A, B, C and D.

If the owners of these 4 databases are the same, an authorized user in the C database can run queries on other databases from a view created in the C database, even if they are not authorized in other databases.

Therefore, it is not safe to activate this feature in consolidated systems. If you are activating, you need to make sure that the owner of the databases is different.

This feature can also be enabled at Server Level. But for security reasons mentioned above, it is useful not to activate this feature. I still share the script below, thinking that there may be a need in some cases.

 

You can use the following script to find the databases where DB CHAINING is active.