A fundamental way that SQL Server determines whether a principal has the permissions necessary to execute code is with its execution context rules. It’s all complicated by the possibility that a principal has permission to execute code but doesn’t have permission on the underlying objects accessed by the code, such as the data in a table. This stairway level will explore SQL Server’s execution context, ownership chains, and impersonation, as well as show you how you can control access to data via T-SQL code.

Execution Context

When a user executes a stored procedure or other database code, SQL Server checks to make sure that the user has permission not only to run the procedure but also to use database objects that the code accesses. Without this kind of permission checking, someone could easily create code that can read tables and use other objects that the user who executes the code doesn’t have access to. This would be allow a major security breach.

The one exception to this permission-checking process occurs when the owner of the code is also the owner of all of the underlying objects that the code accesses. In this case of common ownership, SQL Server verifies that the caller has EXECUTE permissions on the code and does not continue to check permissions.

For example, if the code in a stored procedure accesses three tables and four views, SQL Server performs these conceptual steps before executing the code:

1. Verify that the caller has EXECUTE permission on the code. If the caller doesn’t, return an error and proceed no further.
2. Check whether the code’s owner also owns all objects that the code accesses. If common ownership exists, stop checking permissions and execute the code.
3. If common ownership does not exist, check to make sure that the caller has permissions on the objects that the code accesses. If the caller does not have permissions on any one or more of the objects, return an error and do not execute the code.
4. If the caller has all necessary permissions, execute the code. Otherwise, return an error and don’t execute the code.

This process of common ownership checking continues in the case where code calls other code or accesses other objects, which in turn calls other code or accesses other objects. So long as all of the objects in the chain have the same owner, permission checking is not needed. But as soon as there is an object in the chain with a different owner than the object that accessed it, permissions on that object are checked.

The ownership of objects in this kind of scenario is called an ownership chain, and the lack of common ownership is called a broken ownership chain. This is a very powerful concept in understanding SQL Server security and enhances performance by allowing SQL Server to bypass unnecessary permission verifications.

It is generally easier to write code that has an unbroken ownership chain, because then you don’t have to worry about the security context in which the code executes. This is why it was common in earlier versions of SQL Server to have the special dbo role own all objects. But anytime you have common ownership and permissions to access everything, you violate the principle of least privilege and expose your data to unnecessary security risks.

Fortunately, in SQL Server you can change the security execution context of code.

Changing the Execution Context

You don’t always want the caller’s permissions to be used to validate permissions in a broken ownership chain. Sometimes you want to execute the code as though it was being executed by another user entirely, by using that other user’s permissions to validate permissions on all of the accessed objects. This is called switching the execution context of the code. This lets you take advantage of SQL Server’s granular permissions, keeping tight control over the permissions to underlying objects, while still giving various users the ability to execute the code.

In SQL Server, when you define any kind of user-defined functions (except inline table-valued functions), stored procedures, and triggers, you can use the EXECUTE AS clause as part of the definition of the object, to indicate that the code should be run under the security context of the specified user.

T-SQL makes available four EXECUTE AS options:

• EXECUTE AS CALLER: The default for backward compatibility. The code executes in the context of the caller of the code, who must have permissions both to execute the code and to access underlying objects. The actual behavior depends on whether the ownership chain is broken or unbroken.
• EXECUTE AS = ‘username’ and EXECUTE AS = ‘loginname’: The code executes in the context of the specified user or login, so that the specified user or login must have permissions on all of the underlying objects. In this case, the caller must either:
  • Have EXECUTE permission on the code.
  • Be a sysadmin or db_owner, or have CONTROL SERVER permissions on the server or the database, or have impersonate permission for the username.

The EXECUTE AS option with a loginname applies only to DDL triggers with server scope and to logon triggers. Otherwise, the name provided must be a valid database user name.

• EXECUTE AS SELF: This is a shortcut notation for the current user who is creating the procedure. This is equivalent to EXECUTE = [myUserName]. The SQL Server catalog stores the actual user ID of the person who writes the code.
• EXECUTE AS OWNER: This is another variation of execution under the security context of a specific user, in this case the owner of the code at the time of execution, not at the time of creation. If the owner changes after the code is created in the database, this will mean that the code will execute with permissions of someone different from the original user who first created the code.

There are two variants of EXECUTE AS for use as a statement in the execution context of a session, when you are running code in Management Studio. These are EXECUTE AS LOGIN = ‘loginname’ and EXECUTE AS USER = ‘username’. A session starts when a user logs into an instance of SQL Server; at that time the execution context is set to the logged-in user for the purpose of permission checking. These forms of EXECUTE AS change the execution context for the duration of the session or until the user executes the REVERT statement.

Any time the security context changes through EXECUTE AS, the creator of the code or the session user must have the IMPERSONATE permission for the user specified in the clause. You don’t ever need to have this permission to impersonate yourself, however, such as for EXECUTE AS SELF.

Using the EXECUTE AS Clause

Suppose that you have a Vendor table in a database. The table is defined in the SchemaUserTable schema, which is owned by the UserTable user. The code in Listing 6.1 is the definition of a stored procedure that accesses the table. The procedure is defined in the SchemaUserProc, which is owned by the UserProc user. Because the table and procedure are defined in different schemas owned by different users, a broken ownership chain exists.

USE ExecuteContextDB;
GO
CREATE PROC SchemaUserProc.VendorAccessProc @state CHAR(2)
AS 
    SELECT * FROM SchemaUserTable.Vendor WHERE state = @state;
GO

Listing 6.1: Creating a stored procedure in one schema that accesses a table in a different schema, where the schemas have different owners.

The code in Listing 6.2 grants the EXECUTE permission on the stored procedure to a real user, RealUser, who will be running the code.

GRANT EXECUTE ON SchemaUserProc.VendorAccessProc TO RealUser;
GO

Listing 6.2: Granting EXECUTE permission on the new stored procedure.

Within Management Studio, you can run EXECUTE AS as a statement to temporarily change the security context in which code is running within a query window. Use the code in Listing 6.3 to change the security context to RealUser and run the stored procedure to get a list of vendors located in Alaska.

EXECUTE AS user = 'RealUser';
EXEC SchemaUserProc.VendorAccessProc 'AK';

Listing 6.3: Changing the execution context to run the stored procedure as RealUser.

Executing this code causes the following error:

Msg 229, Level 14, State 5, Procedure VendorAccessProc, Line 66

The SELECT permission was denied on the object 'Vendor', database 'ExecuteContextDB', schema 'SchemaUserTable'.

The problem is that an ownership chain is broken—the owner of the stored procedure is different from the owner of the table—and RealUser doesn’t have SELECT permission on the Vendor table. Here’s how SQL Server conceptually analyzes the situation:

• Caller is RealUser, who has EXECUTE permission. Check!
• Owner of the procedure is UserProc. Owner of the table is UserTable. This indicates a broken ownership chain, so check that the caller, RealUser, has permission to perform the actions in the code.
• RealUser does not have SELECT permission on the Vendor table, so throw an error. Fail!

You can use an EXECUTE AS clause in the stored procedure definition to fix the problem, assuming that you, as the creator of the stored procedure, wish to allow RealUser to execute the code in this situation. First, use the REVERT statement shown in Listing 6.4 to undo the security context switch to RealUser, and return to your own security context:

REVERT;

Listing 6.4: Reverting to the original security context of the user who is running Management Studio.

Next, ALTER the stored procedure to include an EXECUTE AS clause to run the procedure with the security context of UserTable, which has SELECT permission on the table, as shown in Listing 6.5.

ALTER PROC SchemaUserProc.VendorAccessProc @state CHAR(2)
WITH EXECUTE AS 'UserTable'
AS 
    SELECT * FROM SchemaUserTable.Vendor WHERE state = @state;
GO

Listing 6.5: Changing the stored procedure to use EXECUTE AS to change the execution context at runtime.

Then change the execution context to RealUser and try to run the stored procedure again, using the code in Listing 6.6.

EXECUTE AS user = 'RealUser';
EXEC SchemaUserProc.VendorAccessProc 'AK';
REVERT;

Listing 6.6: Testing the updated stored procedure to see if RealUser can now execute the code.

This time the call succeeds, because when SQL Server checks permissions in the ownership chain—which is still broken—it sees that UserTable has the necessary SELECT permission. The results are shown in Figure 6.1.

Figure 6.1: The result of running the stored procedure under a different user’s security context.

Code Signing

Changing the execution context of a block of T-SQL code using the EXECUTE AS clause is just one way to get around issues with broken ownership chains. Another option is to sign the code with either a certificate or an asymmetric key. This technique grants permissions to the code itself rather than requiring that you change the execution context or relying on the caller’s permissions. And by carefully controlling the use of the certificate or asymmetric key, you can still control which principals are able to take advantage of the permissions to execute the code.

The way this works is that you create a secure, encrypted certificate or asymmetric key, then create a user that is associated with the certificate or key. This is a special type of user that isn’t associated with a login. You assign the permissions needed to execute the stored procedure to the user and then use the ADD SIGNATURE statement to assign the certificate or key to a stored procedure. The stored procedure runs with the security permissions of the user who is associated with the certificate or key.

You can use this technique even if the stored procedure changes the execution context using the EXECUTE AS statement. A common scenario for code signing is to change the execution context to a user that has most of the permission that the code needs to execute, then add one or more additional permissions using code signing.

As usual, an example should help to make sense of this technique. The code in Listing 6.7 creates two stored procedures that again retrieve data from the Vendor table in the ExecuteContextDB database. The UnsignedProc procedure will not be signed, so should fail when RealUser executes it. The SignedProc procedure will be signed, so will properly execute for RealUser.

CREATE PROC SchemaUserProc.UnsignedProc @state CHAR(2)
AS
    SELECT * FROM SchemaUserTable.Vendor WHERE state = @state;
GO
CREATE PROC SchemaUserProc.SignedProc @state CHAR(2)
AS
    SELECT * FROM SchemaUserTable.Vendor WHERE state = @state;
GO
GRANT EXECUTE ON SchemaUserProc.UnsignedProc TO RealUser;
GRANT EXECUTE ON SchemaUserProc.SignedProc TO RealUser;
GO

Listing 6.7: Code to create to identical stored procedures and grant the EXECUTE permission on them to RealUser.

But this time, instead of conferring the SELECT permission by changing the execution context, we will create a certificate, as shown in Listing 6.8. The listing then creates a user from that certificate, and grants the SELECT permission on the Vendor table that that user. Finally, the code uses the ADD SIGNATURE statement to add the certificate to the SignedProc stored procedure. Notice that only SignedProc gets signed; UnsignedProc remains unsigned.

CREATE CERTIFICATE MyCertificate
    ENCRYPTION BY PASSWORD = 'SZ6T4O^ff&1Kr3s?m\*'
    WITH SUBJECT = 'Certificate to sign SignedProc';
GO
CREATE USER MyCertificateUser 
    FROM CERTIFICATE MyCertificate;
GRANT SELECT ON SchemaUserTable.Vendor TO MyCertificateUser;
GO
ADD SIGNATURE TO SchemaUserProc.SignedProc BY CERTIFICATE MyCertificate
    WITH PASSWORD = 'SZ6T4O^ff&1Kr3s?m\*';
GO

Listing 6.8: Code to implement a certificate and assign permissions to code.

Finally, it is time to test this code signing scheme, as shown in Listing 6.9. The results are shown in Figure 6.2. UnsignedProc has a broken ownership chain and RealUser doesn’t have SELECT permission on the Vendor table, so execution fails. SignedProc grants the SELECT permission through the use of code signing, and execution succeeds by returning the three Alaska vendors.

EXECUTE AS USER = 'RealUser';
-- Can't run UnsignedProc
EXEC SchemaUserProc.UnsignedProc 'AK';
-- Can run SignedProc
EXEC SchemaUserProc.SignedProc 'AK';
REVERT;

Listing 6.9: Test code signing using a certificate.

Figure 6.2: Results of testing the code signing on the UnsignedProc and SignedProc stored procedures.

Setting all this up is a bit convoluted, but it can be well worth the security benefits. Done correctly, this technique eliminates the need for users to have SELECT permissions on underlying objects in addition to EXECUTE permissions on the stored procedure. It probably isn’t something you’ll use widely in your stored procedures or user-defined functions, but it does nicely solve some security problems when dealing with broken ownership chains when there isn’t a principal handy that has all the needed permissions.

Summary

The simplest way to create stored procedures and user-defined functions in SQL Server is to implement them with an unbroken ownership chain, where the owner of the code also owns all the database objects accessed by the code. But this is often not feasible, when object ownership is distributed among multiple principles in a SQL Server instance. This Level explored two techniques you can use to handle broken ownership chains, by changing the execution context and assigning permissions using code signing. These techniques are complimentary, so you can use them together for a single stored procedure or function. That way, you can handle just about any permissions scheme you’re face with, while keeping you database and its data as secure as possible.