First published on MSDN on Aug 17, 2018
Azure SQL Managed Instance enables you to run cross-database queries the same way you do it in SQL Server. It also supports cross-database ownership chaining that will be explained in this post.
Cross database ownership chaining enables logins to access the objects in other databases on the SQL instance even if explicit access permissions are not granted on these objects, if the logins are accessing the objects via some view or procedure, if view/procedure and the objects in other database have the same owner, and if DB_CHAINING option is turned on on the database.
DB_CHAINING is disabled by default on new databases because you need to be aware what it exactly do and does it violates some security policy in your system before you explicitly enable it.
If you have the same owner on several objects in several databases, and you have some stored procedure that access these objects, you don’t need to GRANT access permission to every object that the procedure needs to access. If the procedure and the objects have the same owner, you can to GRANT permission on the procedure and Database Engine will allow the procedure to access all other objects that share the same owner.
In this example, I will create two databases that have
the same owner
and a login that will be used to access the data. One database will have some table and other database will have a stored procedure that reads data from the table in other database. Login will be granted to execute the stored procedure, but not to read data from the table:
— Create two databases and a login that will call procedure in one database
CREATE DATABASE PrimaryDatabase;
GO
CREATE DATABASE SecondaryDatabase;
GO
CREATE LOGIN TheLogin WITH PASSWORD = ‘Very strong password!’
GO
— Create one database with some data table,
— and another database with a procedure that access the data table.
USE PrimaryDatabase;
GO
CREATE PROC dbo.AccessDataTable
AS
BEGIN
SELECT COUNT(*) FROM SecondaryDatabase.dbo.DataTable;
END;
GO
CREATE USER TheUser FOR LOGIN TheLogin;
GO
GRANT EXECUTE ON dbo.AccessDataTable TO TheUser;
GO
USE SecondaryDatabase;
GO
SELECT * INTO dbo.DataTable FROM sys.objects;
GO
CREATE USER TheUser FOR LOGIN TheLogin;
GO
If you try to read the table via procedure you will get an error because the login don’t have GRANT permission on the table:
EXECUTE(‘SELECT * FROM SecondaryDatabase.dbo.DataTable’) AS LOGIN = ‘TheLogin’ ;
GO
— Msg 229, Level 14, State 5, Line 34
— The SELECT permission was denied on the object ‘DataTable’, database ‘SecondaryDatabase’, schema ‘dbo’.
The same thing will happen if you try to read the data from the table using the stored procedure:
EXECUTE(‘EXEC PrimaryDatabase.dbo.AccessDataTable’) AS LOGIN = ‘TheLogin’ ;
GO
–Msg 229, Level 14, State 5, Procedure dbo.AccessDataTable, Line 5 [Batch Start Line 65]
–The SELECT permission was denied on the object ‘DataTable’, database ‘SecondaryDatabase’, schema ‘dbo’.
Although the user has the rights to execute the procedure, Database Engine will block the query since the login don’t have access rights to read from the underlying table in SecondaryDatabase.
Now, we can enable ownership chaining on the databases:
ALTER DATABASE PrimaryDatabase SET DB_CHAINING ON;
GO
ALTER DATABASE SecondaryDatabase SET DB_CHAINING ON;
GO
If we try to access table again via procedure we are getting the results:
EXECUTE(‘EXEC PrimaryDatabase.dbo.AccessDataTable’) AS LOGIN = ‘TheLogin’ ;
Managed Instance/Database Engine will see that procedure and table have the same owner, and since DB_CHAINING is turned on, it will allow access to the table.
However, note that the login still don’t have rights to access the table directly because nobody granted him access:
EXECUTE(‘SELECT * FROM SecondaryDatabase.dbo.DataTable’) AS LOGIN = ‘TheLogin’ ;
GO
–Msg 229, Level 14, State 5, Line 54
–The SELECT permission was denied on the object ‘DataTable’, database ‘SecondaryDatabase’, schema ‘dbo’.
Conclusion
Database ownership chaining might be useful but also unexpected behavior from the security perspective. You would need to carefully analyze when and do you want to configure it.