Relational databases are used in an amazing variety of applications with connections from a dizzying array of clients over widely distributed networks, particularly the Internet, which makes their data accessible to almost anyone, anywhere. Databases can hold a significant portion of human knowledge, including highly sensitive personal information and critical data that makes international commerce work.

These characteristics make databases attractive targets for people who want to steal data or harm its owner by tampering with it. Making sure that your data is secure is a critical part of configuring SQL Server and developing applications that use it to store data. This Stairway explores the basics of SQL Server 2012 security so that you can protect your data and server resources, getting as granular as you need to be to protect against the unique security threats that can affect your data. Much of the information will apply to earlier versions of SQL Server, going back to SQL Server 2005, because that is when Microsoft overhauled security in the product. But I'll also discuss features that are only in SQL Server 2012 and later.

Most of the time, you'll probably be concerned with securing data and object access within a self-contained database that has a single owner. But sometimes you need to reach outside a database and access data and objects from multiple databases, which raises some security issues and increases the complexity of data access. In this stairway level, you'll learn about cross-database ownership chaining so that you can reach across database boundaries securely.

Ownership Chaining

Most of the time, you'll probably create database objects that reference other objects all contained within the same database. Examples include a stored procedure that accesses tables in the same database, a view that links together tables all in the same database, and so on. But sometimes you'll need to create objects that access other objects across database boundaries. For the most part, the security rules for cross-database access follow the same rules as when you stay within a single database. The accessing user needs to have necessary permissions on the objects they directly access, unbroken ownership chains allow SQL Server to short-circuit permission checking, and so forth. After all, SQL Server can't let down its guard no matter where objects may happen to reside! And as within a single database, ownership chains help simplify security management when used across databases as well.

The basics of how an ownership chain works are the same whether you stay within a single database or cross database boundaries. All database objects have an owner and that owner controls who has what permissions on the objects it owns. Objects that access other objects—such as a stored procedure that joins together multiple tables in a SELECT statement—form an ownership chain that is unbroken as long as a single owner owns all of the objects involved.

The result is that a user who has permission on the top-level object—a stored procedure or a view, for example—who accesses other objects does not need to have permissions on the underlying objects, as long as there is an unbroken ownership chain. SQL Server stops checking permissions on such an object once it verifies that the user has permission on the top-level object. This scheme gives owners more and better control over access to the underlying objects because users need permission only on the direct object that they access.

Cross-Database Ownership Chains

Cross-database ownership chains are an extension of ownership chains where all the objects—both the objects accessed directly by users as well as the underlying referenced objects—are all in a single database. The only difference is that cross-database ownership chains cross database boundaries. So you could have a view in one database that joins together data from tables in multiple databases. Or a stored procedure that accesses objects in multiple databases. In these cases, the source object that the user directly accesses depends on objects contained in another database.

The only significant difference between the two types of ownership chains is the principal who can be the owner of the objects across databases. A database user is a principal that is fully contained within a single database. Even if multiple databases each has a user with the same name, those are separate, distinct principals and so cannot participate in an unbroken ownership chain unless all those users are mapped to the same server-level login. So it is the login who is the relevant owner, not the database users. And that is the key concept: the common owner of objects in an unbroken ownership chain is a server-level principal instead of a database-level principal.

Internally, SQL Server identifies object owners by their security IDs (SIDs) not by name. Within a single database, all objects owned by a single user have a single SID specified as the owner, because there can only be a single user with that name in the database. But across databases, the SID is that of the login that is the ultimate owner at the server level. Different users in different databases can be associated with different logins, and so would have different SIDs. This is probably one of the more potentially confusing aspects of users versus logins, so be sure that you're clear on this point!

An unbroken cross-database ownership chain requires that all of the object owners—source object and all referenced objects—are mapped to the same login with the same SID.

Exploring Cross-Database Ownership Chaining

The sample code for this Level will illustrate how to use cross-database ownership chaining and explore its features. The code begins by creating a login at the server level named SharedLogin as shown in Listing 7.1. Later code will use this as the shared owner for the objects in the ownership chain.

USE master;
GO
IF SUSER_SID('SharedLogin') IS NOT NULL DROP LOGIN SharedLogin;
CREATE LOGIN SharedLogin WITH password = 'Y&2!@37z#F!l1zB';
GO

Listing 7.1: Code to create a SharedLogin login that will be the objects' owner.

We need two databases to be involved in the cross-database chaining, so the code creates those next. The database that contains the object directly accessed by a user will be called SourceDB, and the database with the reference object will be ChainedDB. ChainedDB will contain a single dbo.AlaskaCity table with a small bit of data about the populations of Alaska's three largest cities. The code in Listing 7.2 creates ChainedDB and its AlaskaCity table, and inserts some data into the table, using the 2010 Census data for the population figures.

IF DB_ID('ChainedDB') IS NOT NULL DROP DATABASE ChainedDB;
CREATE DATABASE ChainedDB;
GO
USE ChainedDB;
GO
-- Create a table for access from another database
CREATE TABLE dbo.AlaskaCity
(
    AlaskaCityID INT NOT NULL IDENTITY(1, 1), 
    CityName NVARCHAR(20) NOT NULL, 
    Population INT NOT NULL
    CONSTRAINT PK_AlaskaCity PRIMARY KEY (AlaskaCityID)
);
GO
INSERT INTO dbo.AlaskaCity (CityName, Population)
    VALUES ('Fairbanks', 31535), ('Anchorage', 291826), ('Juneau', 31275);
GO

Listing 7.2: Code to create the ChainedDB database and the AlaskaCity table with sample data.

Next the code creates the SourceDB that contains a view that a user will directly access in order to retrieve the data from AlaskaCity in ChainedDB. Listing 7.3 shows that code, and includes a SELECT statement to test that everything is working okay. This statement should work fine if you're logged in as a sysadmin, since you have full access to all objects in the SQL Server instance and own all the objects created so far as dbo, and you should see the Alaska population data. Running as sysadmin is convenient, but it isn't secure for production use, and normally a user accessing code won't have ownership of all objects!

IF DB_ID('SourceDB') IS NOT NULL DROP DATABASE SourceDB;
CREATE DATABASE SourceDB;
GO
USE SourceDB;
GO
-- Create a view that accesses ChainedDB.dbo.AlaskaCity
CREATE VIEW dbo.AlaskaCitiesView AS
    SELECT * FROM ChainedDB.dbo.AlaskaCity;
GO
SELECT * FROM dbo.AlaskaCitiesView ORDER BY Population DESC;

Listing 7.3: Code to create the SourceDB database and a view that accesses the AlaskaCity table in ChainedDB.

At this point we have two databases, one with an object that references another object in another database, and it all works running as sysadmin. So now let's make it a more realistic example and break the code. The code in Listing 7.4 creates a SourceUser in the SourceDB database that is mapped to SharedLogin, and grants the SELECT permission on the AlaskaCitiesView view to the user. Then the code changes the execution context to SharedLogin and attempts to access the view. Will the SELECT statement succeed?

USE SourceDB;
GO
-- Create a user in the SourceDB who will access the view
CREATE USER SourceUser FOR LOGIN SharedLogin;
GRANT SELECT ON dbo.AlaskaCitiesView TO SourceUser;
GO
-- Try accessing the view as SourceUser
EXECUTE AS LOGIN = 'SharedLogin';
SELECT * FROM dbo.AlaskaCitiesView ORDER BY Population DESC;
GO
REVERT;

Listing 7.4: Code to create the SourceUser database user mapped to the SharedLogin with code to test access to the view.

No, the SELECT statement throws an error: ‘The server principal "SharedLogin" is not able to access the database "ChainedDB" under the current security context.' Why not?

Let's step back a moment. We have an unbroken cross-database ownership chain: the view and table share common ownership, which is my (sysadmin) login that is mapped to dbo in both databases. We're good there. But cross-database ownership chaining is not enabled in the server instance, nor is it enabled for either of the new databases. So next we need to enable it.

Enabling Cross-Database Ownership Chaining

The option to use cross-database ownership chaining is off by default in a fresh installation of an instance of SQL Server. This is because enabling the option opens a few chinks in the security armor of the instance; I'll briefly cover the risks later in this Level. When the option is disabled, SQL Server will generate permission-denied errors when code relies on cross-database ownership chaining. (As you'll see later, this isn't the only problem that needs correction before the code will work, but it's the first one we'll tackle.)

You can enable cross-database ownership chaining either at the server level or at the database level, using either T-SQL statements for either or Management Studio at the server level.

Enabling at the Server Level

Enabling cross-database ownership chaining at the server level enables the option for all databases. If you enable it, it is on for all databases and you can't restrict it at the database level.

Using Management Studio, right-click on the server instance in Object Explorer and select Properties from the pop-up menu. Select the Security page and you'll see the Cross-database ownership chaining option near the bottom of the dialog box, as shown in Figure 7.1. Click OK, and all databases in the instance will have it enabled.

Figure 7.1: Enabling cross-database ownership chaining using the Server Properties dialog box.

You can do the same thing with T-SQL code, as shown in Listing 7.5. Like other server instance options, setting the cross db ownership chaining option to 1 enables it and setting it back to 0 disables it. Be sure to use the RECONFIGURE statement so that you don't need to restart the instance to have the change take effect. Once you execute that code, you can use cross-database ownership chaining across any databases in this SQL Server instance.

USE master;
GO
EXECUTE sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;

Listing 7.5: Enabling cross-database ownership chaining using the sp-configure system stored procedure.

If you enabled cross-database ownership chaining using either Management Studio or T-SQL code, turn it back off now. This is not the best way to enable it, unless you really are going to reference objects from source objects in every database in the instance. If you're not, enabling it at the instance level is far too insecure and is not a good idea.

Instead, enable cross-database ownership chaining only for the databases where you need it.

Enabling at the Database Level

If cross-database ownership chaining is disabled at the server level you must enable it at the database level if you want to use it. If it is disabled for a database, the database can't participate in cross-database ownership chaining at all, as either the source or the chained database. For chaining to work, both the source and chained database must have the setting enabled.

By default, cross-database ownership chaining is disabled for all user databases when you create or attach them. But it is enabled for the master, msdb, and tempdb system databases, and is disabled for the model database. Because SQL Server uses cross-database ownership chaining internally, you can't enable or disable it for these system databases.

Unfortunately, Management Studio has the Cross-database Ownership Chaining Enabled option disabled in the Database Properties dialog box, as you can see in the Options page in Figure 7.2, making it read-only there. To change the value for a database, you'll need to use code like that in Listing 7.6, which enables the setting for both the SourceDB and ChainedDB databases. This code uses the SET DB_CHAINING option of the ALTER DATABASE statement to turn chaining on or off.

Figure 7.2: Viewing the read-only setting for cross-database ownership chaining for the ChainedDB database.

ALTER DATABASE ChainedDB SET DB_CHAINING ON;
ALTER DATABASE SourceDB SET DB_CHAINING ON;
GO

Listing 7.6: ALTER DATABASE code to enable cross-database ownership chaining in the two databases.

With cross-database ownership chaining enabled in both databases, you can try to access the view again with SharedLogin's security context; Listing 7.7 shows that code again.

USE SourceDB;
GO
EXECUTE AS LOGIN = 'SharedLogin';
SELECT * FROM dbo.AlaskaCitiesView ORDER BY Population DESC;
GO
REVERT;

Listing 7.7: Code that attempts to use cross-database ownership chaining to access objects across database boundaries.

And…the code still fails with the same error message that SharedLogin can't access ChainedDB under the current security context. The problem this time is that there is one other requirement for this to work: the user accessing the view object has to have some access to the chained database. The user doesn't need to have permissions on the underlying objects. In fact, the user doesn't need to have any permissions in that database at all.

Listing 7.8 shows how you can resolve this problem. The code creates a ChainedUser in the ChainedDB database that is mapped to SharedLogin. This gives SharedLogin a toehold in that database, which is all that is necessary for the cross-database ownership chaining to work. To verify that SharedLogin has no SELECT permission on the AlaskaCity table, the code attempts to directly read the data from AlaskaCity. But that attempt fails because SharedLogin and ChainedUser have no permissions on any object in ChainedDB. This time the error is “The SELECT permission was denied on the object 'AlaskaCity', database 'ChainedDB', schema 'dbo',” as you'd expect.

USE ChainedDB;
GO
CREATE USER ChainedUser FOR LOGIN SharedLogin;
-- Note that we're not granting the user any permissions in the chained database.
GO
-- Verify that SharedLogin doesn't have direct access to the AlaskaCity table, even in the ChainedDB database context.
EXECUTE AS LOGIN = 'SharedLogin';
SELECT * FROM dbo.AlaskaCity;
GO
REVERT;

Listing 7.8: Code that creates a user mapped to SharedLogin in ChainedDB but without any permissions, which is verified by the attempt to select data from AlaskaCity.

Now you can execute the code from back in Listing 7.7, which finally succeeds in retrieving the data from the view. At last!

You can verify that cross-database ownership chaining has to be enabled, even with common object ownership and a user that has access to both databases by turning off cross-database ownership chaining using the code in Listing 7.9—actually you only need to run either of the statements, since the setting has to be ON for both databases for cross-database ownership chaining to work–and then running the code back in Listing 7.7 yet again. This time you get the error that “The SELECT permission was denied on the object 'AlaskaCity', database 'ChainedDB', schema 'dbo'.” This is the actual error that cross-database ownership chaining overcomes.

ALTER DATABASE ChainedDB SET DB_CHAINING OFF;
ALTER DATABASE SourceDB SET DB_CHAINING OFF;
GO

Listing 7.9: Code to turn off cross-database ownership chaining for the two databases.

Common Ownership

The reason why the view in the sample code works with cross-database ownership chaining enabled is that the view and the table have common ownership, the SharedLogin login. The sample code was written with the assumption that the user logged into Management Studio is a sysadmin, so all the objects created in the code are owned by the dbo user in both databases, both of which are mapped to the sysadmin user. (But this is not required for cross-database ownership chaining to work. It's just a way to keep the sample code simple and focused.) You can verify the mappings this using the code in Listing 7.10, which lists the schema name, owner user name, and owner login name for user databases and views in a database. If you run this code in SourceDB and ChainedDB, you'll find that the OwnerLoginName values are identical, as showin in Figure 7.3 and Figure 7.4. In my case, the dbo user is mapped to the login Marathon\Don.

SELECT
    so.[name] AS Object, 
    sc.[name] AS [Schema], 
    USER_NAME(COALESCE(so.principal_id, sc.principal_id)) AS OwnerUserName, 
    sp.name AS OwnerLoginName, 
    so.type_desc AS ObjectType 
FROM sys.objects so 
    JOIN sys.schemas sc ON so.schema_id = sc.schema_id 
    JOIN sys.database_principals dp ON dp.principal_id         = COALESCE(so.principal_id, sc.principal_id)
    LEFT JOIN master.sys.server_principals sp ON dp.sid = sp.sid
WHERE so.[type] IN ('U', 'V');

Listing 7.10: Code that lists the schema name, owner user name, and owner login name for user databases and views.

Figure 7.3: Result of running code in Listing 7.10 in the SourceDB database.

Figure 7.4: Result of running code in Listing 7.10 in the ChainedDB database.

It's important to understand that the dbo user in one database is not necessarily the same as the dbo user in another database. They could be mapped to entirely different logins or none at all. What is important is that the owner of record for all the objects involved in the chain have the same SID. You can use code like that in Listing 7.11 to verify that the object owner SID is the same as the login. (You'll need to change the machine and user name in the last statement if you run the code yourself, unless your name happens to be is Don and you're on a machine named Marathon.)

SELECT name, sid FROM SourceDB.sys.database_principals WHERE name = 'dbo'
UNION ALL
SELECT name, sid FROM ChainedDB.sys.database_principals WHERE name = 'dbo'
UNION ALL
SELECT 'MARATHON\Don', SUSER_SID('MARATHON\Don');

Listing 7.11: Code to verify that the SIDs of the dbo users are the same as that of the mapped login.

On my local development machine, the code in Listing 7.11 results in the data shown in Figure 7.5. Common ownership!

Figure 7.5:Security IDs for the dbo users in SourceDB and ChainedDB, and the login executing the code in this Management Studio session.

Risks of Cross-Database Ownership Chaining

There is an element of risk by enabling cross-database ownership chaining because of potential abuse by highly-privileged users. Microsoft characterizes the risk in two ways, both of which involve the potential to cross the security boundary of a database:

• In the context of a single database, the database owner and members of the db_ddladmin and db_owners database roles can create objects owned by other users. These new objects can use objects in other databases that have the same owners, unintentionally giving access to objects in the other databases. In essence, you need to trust these privileged users with the data in all databases, depending on how you have security configured in the instance of SQL Server.
• Database users with the CREATE DATABASE permission can create new databases and attach existing databases to an instance of SQL Server. With cross-database ownership chaining enabled, these users could access objects in other databases from those newly created or attached databases.

The key here is that you need to trust your highly privileged users, those with permissions beyond simply accessing and maintaining data in specific tables, if you're going to allow cross-database ownership chaining. This is one reason why Microsoft highly recommends that if you need to enable cross-database ownership chaining you do it only for the databases where you need it in order to contain the security risks.

Summary

Cross-database ownership chaining is yet another way that SQL Server helps keep your data secure. With this option disabled, it is harder for malicious users to access data in other databases. But in the right scenarios and security environments, you can enable this option to let database object owners keep tighter control of their data. You should rarely if ever enable cross-database ownership chaining at the server level. Instead, enable it only for the databases where you really need it and be sure to protect against its abuse by highly privileged users.