Packaging Permissions in Stored Procedures

Packaging Permissions in Stored Procedures

An SQL text by Erland Sommarskog, SQL Server MVP. Most recent update 2017-12-03.
Copyright applies to this text. See here for font conventions used in this article.

Introduction

In this article you will learn you how you can package permissions for an action in SQL Server inside a stored procedure. That is, rather than granting the permissions the action requires directly to users and thereby permit them to do a lot more things they should not be allowed to, you grant them rights to run a stored procedure which performs this specific action but not more than that. A simple example in an application is that far-reaching updates to a customer record should only be permitted for managers. An example from the DBA world is that you have power users who need be able to see who are connected to their database, but they must not see other users on the system.

For this packaging task, we have three methods to our disposal:

  1. Ownership chaining.
  2. Certificate signing.
  3. The EXECUTE AS clause.

The by far the most important and simplest to use of these is ownership chaining. In fact, it is so simple that you may not be aware of that you are using it. Simple as it may be, it is limited to a quite specific but very common scenario that accounts for 95 % or more of our stored procedures. It is only for the extra special ones that we need to employ the other two methods. Both serve the same purpose, but they do so in different ways. Of the two, certificate signing is preferable in most cases. It may seem overly complex at first, but we will learn how we can overcome that by automation. EXECUTE AS appears at first glance to be a lot easier to use, but this is a mirage: you need good understanding of your system to know whether you can use EXECUTE AS without causing undesired side effects. When it comes to server-level permissions, using EXECUTE AS requires that the database to be set as TRUSTWORTHY, and you will learn that this can introduce a major security vulnerability on your server.

This article consists of two parts: the main article, which you are reading right now, and an appendix. In the main article, you will learn to use these three techniques, for database-only permissions, for server-level permissions and for cross-database access. The appendix is an catalogue of examples that show how these techniques can be applied to specific problems.

The main article is quite long. One reason for this is that I illustrate about everything I discuss with code samples and the output from the samples. Another is that I have taken some space to highlight important security risks that you need to be aware of. You may have been referred to this article in response to a question that you need urgent help with. For that situation, I would like to give some reading advice.

Table of Contents

Introduction

Notes on Scripts, Objects, Databases etc

Applicable SQL Versions

Some General Considerations on Security

Multiple Lines of Defence

SQL Injection

Separation of Duties

Impersonating Users

Ownership Chaining

General Setup

How Ownership Chaining Works

Limitations with Ownership Chaining

Owners and Schemas

A Security Threat with Ownership Chaining

Using Certificate Signing in the Database

Creating the Certificate

Signing the Procedure

Creating a User from the Certificate

Granting Permissions to the Certificate User

But What Happened?

The Scope of the Certificate Token

Certificate Signing and DENY

Certificate Management

Variations on the Theme

Using Certificates to Package Server-Level Permissions

A Scenario

Signing Stored Procedures in the master Database

Server-level Permissions in a User Database

Automating Signing for Server-Level Permissions

One More Level: Availability Groups

Interlude: Access to the Database Only through the Application

Application Roles

Application Login

Terminal Server/Citrix

Using EXECUTE AS on Database Level to Package Permissions

The Recipe

Using EXECUTE AS OWNER

The Ramifications of Impersonation

Various Titbits

EXECUTE AS and Server-Level Permissions

Into the Sandbox

The Dangers of TRUSTWORTHY

Server-level Objects

Other Side Effects of Impersonation

Cross-Database Access

Granting Direct Permissions

Enabling the guest User

Certificate Signing

Ownership Chaining

EXECUTE AS + TRUSTWORTHY

Conclusion on Cross-Database Access

Conclusion and Acknowledgements

Revision History

Notes on Scripts, Objects, Databases etc

This article contains a lot of code snippets. With the exception of a few snippets that are only for demonstrational purposes, the snippets are collected in scripts with about one script per chapter. There are links to the scripts as they are needed, and you can copy and paste from your browser to SSMS, if you like. You can also download this zip-archive which contains all scripts so that you have them locally. This archive includes files both for the main article and the appendix.

There is about one script per chapter. The names of the scripts reflect which chapter they belong to. (The scripts of which the name start with app_ relates to the appendix.) Beware that the script for one chapter in the main article generally assumes that you have run the scripts for previous chapters. That is, databases, users, tables, stored procedures are reused. Thus, if you skip a chapter, you should still run the script for that chapter, but you can run all in one go.

In the scripts, there are long lines of hyphens that delimit the snippets in the article. In a few scripts there are also sections delimited by equal signs. They illustrate that you should change context to a different user or server as explained in the chapter. Since the scripts are supposed to be used with the article, they are largely uncommented.

The download also includes some utility scripts that I don't include in whole in the text, but only discuss and drop a link to. These scripts are commented.

The code in this article creates quite a few things on your server: databases, logins, etc. Therefore, I recommend that you run the code on your private instance. By all means, do not run the code on a production server! I should also point out that I assume that when you run the scripts, you are logged in with sysadmin permission. After all, you are playing the server-level DBA.

To produce the output from the scripts, I have been running SSMS set to Results to Text to make it easier to present both result sets and error messages. When you run the scripts yourself, you may prefer to run with output to grid, but you will need to switch between the Results and Messages tabs to see all the output.

At any time you feel that you don't want these objects around anymore, you can use the script CleanupAll.sql, which drops everything created in this article.

All databases created in this article includes the string Play in the name, which can help you to identify whether a database on your server may come from this article. Moreover, check before you start playing whether you have databases with this string – in that case, you need to be careful.

...and as you read on, you may sense a theme related to some guys from Liverpool.

Applicable SQL Versions

This article applies to SQL 2005 and later. SQL 2005 was the version where certificates and EXECUTE AS were introduced, and I will not discuss older versions. Later releases have brought us some smaller additions to these features. The code in this article was written and tested on SQL 2012 and SQL 2017. The scripts include features that do not run on SQL 2005/2008. I will call these out when they appear.

Some General Considerations on Security

This is an article about security features, and therefore it is apt to start with some general considerations on security. There is no script for this chapter, and the snippets are not to be intended to be executed.

Multiple Lines of Defence

Security is one of those difficult things, because it often conflicts with other interests in the programming trade. Users are screaming for a solution and they want it now, and at this point they don't worry about security, they only want to get their work done. But if you provide them with a solution that has a security hole and that hole is later exploited by a rogue employee or an hacker from the outside, guess who will get the blame. Therefore, you must always develop with security in mind.

A common mistake is to think we have this firewall/encryption/whatever in place, so we are safe. In on my point of view, security consists of multiple lines of defence, so if one line is broken, the intruders are stopped by the next line, or at least restricted in how much damage they can inflict. An important thing to keep in mind is that computer systems are not static, but typically they undergo a lot of changes during their lifetime, both in how they are configured and how the code is written. If the system has a single line of defence, it only takes one bad programmer that makes a casual change to open a wide hole. Or one casual DBA to make a configuration change that takes down the line entirely.

This is not the place to discuss all lines of defence you should have in place, but I will just give a quick list of some items:

You may object that in our application users need to be able to create tables, and.... Well, this article is exactly about that. How you can package privileged operations that go beyond the plain vanilla DML access in a stored procedure so that you don't have to grant users (or application logins) elevated permissions.

SQL Injection

I want to highlight one specific security vulnerability and that is SQL injection, which is one of the most commonly exploited security holes. SQL injection is possible when an application builds an SQL statement concatenating SQL syntax with data from user input. Here is a simple example in C#:

cmd = "SELECT * FROM dbo.customers WHERE name LIKE '%" + TextBox1.Value + "'%"

A malicious user can exploit this by entering something quite different from a customer name, for instance:

==' UPDATE dbo.employess SET salary = 10 WHERE name = 'BigBoss' --

The purpose the initial == is only to prevent a lot of data coming back. The key character is the single quote. This terminates the SELECT query, and the user can now enter any statement. The closing dashes comment out any remaining syntax in the original statement. Exactly what the user is able to do in the injected SQL depends on the next line of defence: that is, what permissions have been granted.

You may say to yourself: The user does not know what the SQL statement looks like, and it will take forever to figure out how to exploit a hole. Please remember that today hacking sites is a professional trade that pays well. These people have lot of time on their hands and good tooling to help them to trawl sites to find injection holes. This is anything but a hypothetical threat.

Before I go on, I should say that the correct way of writing the above is:

cmd = "SELECT * FROM dbo.customers WHERE name LIKE '%' + @searchstr + '%'"
cmd.Parameters.Add("@searchstr", SqlDbType.NVarChar, 50).Value = TextBox1.Value

That is, rather than building an SQL string from input, you use a parameterised statement which is a constant SQL string and which thus cannot be manipulated. Not only is your code now safe from SQL injection, but there are also a few pleasant side effects: 1) The plan cache in SQL Server is used more efficiently. 2) Users can now search for O'Brien without hiccups. 3) The code is easier to read and maintain.

This article is about stored procedures, and it is not uncommon to use in dynamic SQL in stored procedures. In stored procedures, too, you should use parameterised SQL statements, and the above would read like this in T‑SQL:

EXEC sp_executesql 
     N'SELECT * FROM dbo.customers WHERE name LIKE ''%'' + @searchstr + ''%''',
     N'@searchstr nvarchar(50)', @searchstring

However, it only takes one junior developer who haven't grasped the concept, and suddenly you have something that looks like this:

EXEC('SELECT * FROM dbo.customers WHERE name LIKE ''%''' + @searchstring + '''%''')

And the exploit above is again possible. This is a theme I will return to more than once in this article to underscore why you should not grant more permissions than needed for the task.

Separation of Duties

In some shops, particularly smaller ones, the same user(s) may be responsible for administer things on server level and database level. That is, there is no distinction between the server-level DBA and the database-level DBA.

However, this is far from always the case. In a large corporation, an instance could have databases for different and unrelated department-level applications. At a hosting service, the databases could belong to customers who are unrelated to each other, or for that matter fierce competitors. The staff who work as DBAs administer the server, but they are not much involved with the individual databases, but they are administered by application admins, the developers, or some other people. They are member of db_owner, or have other elevated permissions on database level, but they typically have no permissions at all outside of their database, nor should they have – except maybe being plain users of other applications.

I have written this article with the latter scenario in mind, not because this is better than the first, but simply because it presents more challenges. The terminology I have adopted is that I talk about the server-level DBA and the other group of people as (local) power users. I often address you as the reader as if you are the server-level DBA. A recurring theme when we come to package server-level permissions in stored procedures is that you must be careful so that you don't introduce security holes that permit these local power users to elevate their permission to server level and become sysadmin.

Impersonating Users

This section is more a preparation for the rest of the article, although what you will learn here is something you can use in general.

In this article we will run many commands as users with limited permissions to test whether permissions work or not. While you could run your DBA commands from one query window, and be logged in as a plain user from a different window, the scripts make use of two very practical statements in SQL Server: EXECUTE AS USER/LOGIN. You can say one of:

EXECUTE AS LOGIN = 'LadyMadonna'
EXECUTE AS USER = 'LadyMadonna'

These statements make you become the user Lady Madonna for all practical matters. Her permissions will apply, and SYSTEM_USER and other functions will return LadyMadonna. Only the function original_login() will reveal that you were the one who logged in originally.

To return to your true self, use the REVERT statement. Note that it must be issued from the same database as you issued the original EXECUTE AS statement. Thus, if you switched to another database while you were Lady Madonna, you need to switch back before you revert. Also, be careful to always put REVERT in a batch of its own like this:

EXECUTE AS USER = 'LadyMadonna'
-- Do something as Lady Madonna
go
REVERT

There is no syntactical requirement that REVERT must be in a separate batch. But if any of the statements you perform as Lady Madonna fails, the batch may be aborted, in which case a REVERT statement at the end of the batch will not be executed. This can cause some confusion until you understand that you still are Lady Madonna. When in doubt, you can always run REVERT a few times extra. REVERT never gives an error message, even if there is nothing to revert from.

The difference between the two commands is that EXECUTE AS LOGIN impersonates a server-level login whereas EXECUTE AS USER impersonates a user on database level only. If you have the choice, use EXECUTE AS LOGIN. When you use EXECUTE AS USER there can be some confusion, as you are sandboxed into the current database and cannot access other databases or perform actions that require server-level permissions – even if the user you impersonate has the permissions required. (This is something we will look more into in further chapters in the article.) With EXECUTE AS LOGIN there are no such restrictions. That said, as long as you only want to test permissions inside the database, the two works equally well.

There are two situations when you would use EXECUTE AS USER over EXECUTE AS LOGIN:

For simplicity, we will only work with SQL logins in this article. But you can also impersonate windows users with EXECUTE AS. Note that you can do this, even if they don't have a login on the server, as long as SQL Server can find them in the AD. They must have access to the database you want to execute in, though. (But again, this can be arranged without a server-level login.) Note also that when you impersonate a Windows user, you should not enclose the name in brackets. That is, use

EXECUTE AS USER = 'Domain\User'

and not any of these:

EXECUTE AS USER = [Domain\User]
EXECUTE AS USER = '[Domain\User]'

The error message for the latter is not very clear.

Note also that you cannot impersonate a Windows group; you will need to pick a member in the group to impersonate.

Ownership Chaining

In this chapter we will learn about ownership chaining, how it works and what the limitations there are. I will also discuss a potential security trap with ownership chaining.

General Setup

The script for this chapter is 03_ownershipchain.sql. The scripts starts with a general setup that we will use all through this article:

SET XACT_ABORT, NOCOUNT ON
USE tempdb
go
IF db_id('Playground') IS NOT NULL
   DROP DATABASE Playground
go
CREATE DATABASE Playground
ALTER AUTHORIZATION ON DATABASE::Playground TO sa
go
USE Playground
go
CREATE ROLE Playrole
GRANT EXECUTE ON SCHEMA::dbo TO Playrole
CREATE USER Michelle WITHOUT LOGIN
ALTER ROLE Playrole ADD MEMBER Michelle

We start off with setting XACT_ABORT so that most errors will abort a batch directly (this is something I discuss in detail in my series Error and Transaction Handling in SQL Server) and we turn on NOCOUNT to save us the noise of the rows affected messages. Next, we move to tempdb before dropping any existing database with the name Playground so that we easily can start over. (If you already have a database with that name – be careful!) We (re)create the Playground database and we make sa the owner of the database. While it is not uncommon to find servers where sa owns all databases, I like to point out that this is not best practice. I will return to why I think so and what I think is best practice, when we discuss the TRUSTWORTHY feature. But for now, sa owns the database.

We now move over to the newly created database and create a role, Playrole, which we grant permission to run all stored procedures in the dbo schema. Finally we add a user, Michelle, whom we make a member of Playrole. Since we will use her to test database permissions only, we create her WITHOUT LOGIN.

Note: The command ALTER ROLE ADD MEMBER was introduced in SQL 2012. If you are on SQL 2008/2005, you will need to use sp_addrolemember to add Michelle to Playrole.

How Ownership Chaining Works

We will now look at the first method to package permissions in a stored procedure, that is, ownership chaining. This is something that has been in the product from day one. The principle is this: If a user U runs stored procedure S which accesses the table T, and S and T are owned by the same user, there is no permission check performed on T at all. Or more generally: if the user U runs a module M which access the object O, and M and O have the same owner, no permissions are checked. A "module" here can be a stored procedure, a user-defined function, a trigger or a view, while the most common examples of "objects" are tables, views, stored procedures and user-defined functions. (There are others, but there is no need to bury us in details.)

This may sound a little abstract, but in very many databases all objects are owned by the user dbo, which also is the owner of the database (dbo is short for database owner), and in that case the rule can be simplified: if users have permission to run a stored procedure, they don't need permissions to tables or other objects accessed by the procedure.

Let's take a look at this in practice. First we need a table to play with:

CREATE TABLE Playtable (id       int          NOT NULL,
                        somedata nvarchar(40) NOT NULL,
                        whodidit sysname      NOT NULL
                            CONSTRAINT Play_default_whodidit DEFAULT USER,
                        CONSTRAINT pk_Play PRIMARY KEY (id)
)

It's a very simple table with an id, a token data column and an auditing column whodidit. Next, we create a simple procedure to insert a row into Playtable and select the inserted data.

CREATE PROCEDURE add_playdata @id int, @somedata nvarchar(40) AS
   INSERT Playtable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM Playtable WHERE id = @id

Now we want to try this as Michelle. Let's first see what happens if Michelle tries to insert a row into the Playtable directly, giving someone else the blame:

EXECUTE AS USER = 'Michelle'
INSERT Playtable(id, somedata, whodidit)
   VALUES (1, 'Des mot qui vont très bien ensamble', 'Maggie Mae')
go
REVERT

This fails with the error message:

Msg 229, Level 14, State 5, Line 33

The INSERT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.

This is of course not unexpected, since no one, not Michelle, nor Playrole, nor Maggie Mae or anyone else has been granted any permissions whatsoever on Playtable. It is only members of elevated roles like db_owner who can access it directly. Michelle now tries running the stored procedure, bitterly accepting that she will have to take full responsibility herself:

EXECUTE AS USER = 'Michelle'
EXEC add_playdata 1, 'Des mot qui vont très bien ensamble'
go
REVERT

We see this output:

id    somedata                             whodidit

----- ------------------------------------ -----------

1     Des mots qui vont très bien ensamble Michelle

That is, Michelle was now able to insert a row in the table and also read the data, despite not having any permissions on the table. Through her membership in Playrole, she had permission to run add_playdata, and because dbo owns both the table and the procedure, no permissions were checked, and the INSERT and SELECT succeeded. Before we go on, note that the whodidit column has the value Michelle; that is, the built-in function USER returns the name of the impersonated user.

I doubt that the reader feels a sense of wonder at this point, since this is something many SQL developers make use of every day – although, they may not be fully aware of the exact mechanism. But let us prove that ownership chaining actually exists. We create a new database-local user NewOwner and make it the owner of Playtable and let Michelle make a second attempt to run add_playdata:

CREATE USER NewOwner WITHOUT LOGIN
ALTER AUTHORIZATION ON Playtable TO NewOwner
go
EXECUTE AS USER = 'Michelle'
EXEC add_playdata 2, 'Words that go well together'
go
REVERT

This produces this output:

Msg 229, Level 14, State 5, Procedure add_playdata, Line 2

The INSERT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.

That is, now that the procedure and table owners are different, there is a permission check. See now what happens when NewOwner also becomes the owner of add_playdata:

ALTER AUTHORIZATION ON add_playdata TO NewOwner
go
EXECUTE AS USER = 'Michelle'
EXEC add_playdata 2, 'Words that to well together'
go
REVERT

Michelle can now successfully insert the row, since the procedure and the table again have the same owner.

As having other owners than dbo is a bit exotic, we restore ownership of both objects to dbo before we continue:

ALTER AUTHORIZATION ON Playtable TO SCHEMA OWNER
ALTER AUTHORIZATION ON add_playdata TO SCHEMA OWNER

You may wonder why I use SCHEMA OWNER here rather than dbo. There is a subtle difference which matters if the objects are later moved to a schema owned by a different user. If I would use dbo, dbo would remain owner of the objects after the transfer to the new schema, whereas SCHEMA OWNER means exactly that. Whoever owns the schema owns the object.

Limitations with Ownership Chaining

From what I have said so far, it may sound like ownership works universally for all features, but this is not the case. In this section we will look at situations where you may expect ownership chaining to work, when it does not.

"Advanced" permissions

Ownership chaining only applies to permissions that you would grant to plain users, that is SELECT, INSERT, UPDATE, DELETE and EXECUTE permissions. Or said in another way, it applies to DML statements like SELECT, INSERT, UPDATE, DELETE and MERGE and when you use EXECUTE to run stored procedures or user-defined functions.

But for any other operation beyond this, SQL Server will always perform a permission check, which means that the user must have been granted the permissions needed – or this has been arranged for through any of the two other methods we will look at later in this article. As one example, consider this procedure which is a possible trap:

CREATE PROCEDURE truncate_playdata AS
   TRUNCATE TABLE Playtable
go
EXECUTE AS USER = 'Michelle'
EXEC truncate_playdata
go
REVERT

Logically, nothing seems wrong here, since this is a procedure to delete all data in the table, and DELETE permission is something that is covered by ownership chaining. Nevertheless, this fails with this error message:

Msg 4701, Level 16, State 1, Procedure truncate_playdata, Line 2

Cannot find the object "Playtable" because it does not exist or you do not have permissions.

This is because the command TRUNCATE TABLE requires ALTER permission on the table, and for such a strong permission, SQL Server never suppresses the permission check, and thus Michelle needs ALTER permission herself to be able to run truncate_playdata.

Dynamic SQL

This is something that has surprised more than one developer. Consider this example with very bad dynamic SQL:

CREATE PROCEDURE dynamic_playdata @searchstr nvarchar(MAX) AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT * FROM dbo.Playtable
                  WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
   EXEC(@sql)
go
EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata 'ensamble'
go
REVERT

The error message is:

Msg 229, Level 14, State 5, Line 72

The SELECT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.

The SQL code in @sql is not part of the stored procedure dynamic_playdata, but rather it is a nameless stored procedure which does not have an owner (or the owner can be said to be the current user). Whatever, the ownership is not inherited from the calling procedure, and therefore there is a permission check.

At first, this may seem like an irritating limitation. Wouldn't it be great to able to package a string of a dynamic SQL in a stored procedure to give the user the possibility to run a dynamic search without permissions on the table? Well, we will learn how to do that in the next chapter. But there is a good reason why ownership chaining does not apply here, and the reason is exactly that too many developers write bad dynamic SQL which is open for SQL injection. Note that the sloppy programmer who wrote dynamic_playdata declared @searchstr to be nvarchar(MAX), despite that nvarchar(40) would have sufficed. Had ownership chaining applied here, an attacker could use the procedure to access any table in the database to his or her own liking, even if the attacker only has permissions to run stored procedures.

Metadata Access

Here is another situation where things do not work as you might expect. We want to add a check that Playtable exists before attempting an operation on it, to save the user from an ugly error message in favour of something more friendly.

CREATE PROCEDURE check_playdata @id int AS
   IF object_id('Playtable') IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'

Michelle tries this procedure:

EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT

But the jaw drops when she sees:

Playtable has not been created yet. Try again later.

The reason for this surprise is that ownership chaining does not apply to access to metadata, be that through built-in metadata functions like object_id, direct access to catalog views like sys.objects or system procedures like sp_help. In all these cases, the permission VIEW DEFINITION on the object in question must be present, one way or another.

Access Outside the Database

Ownership chaining never applies to access on server level, but for any operation that requires permission on server level, SQL Server always performs a permission check.

When it comes to cross-database access, ownership chaining can apply. However, by default, ownership chaining across databases is turned off. We will look at this more in the chapter Cross-Database Access.

Owners and Schemas

In my experience, the by far most common arrangement is that all objects in a database are owned by dbo, and I will go as far as label scenarios where you have multiple owners as "advanced" and not discuss them in this article – save for one situation that I will discuss in an instant.

The situations for schemas is slightly similar. That is, there are plenty of databases where all objects are in the dbo schema. Partly this is due to legacy, since up to SQL 2000 owners and schema were the same thing in SQL Server. But that was long ago and I in my perspective, using multiple schemas is nothing to be considered as advanced. Schemas can serve as namespaces and help you modularise your database. They can also serve as security boundaries, since you can grant permissions on schema level. This is something we have already made use of in the Playground database, where we granted Playrole EXECUTE permission on the dbo schema. We will see the benefit of this further on in the article.

There are some different opinions on what is best practice. Some people frown on:

SELECT id, somedata FROM Playtable

and think that you should always include the schema:

SELECT id, somedata FROM dbo.Playtable

If you look at the code you have seen in this article so far, you may or may not have noticed a pattern. If you look at code where I use dynamic SQL, be that in a stored procedure or from a client, I do say dbo.Playtable. The reason for this is that Playtable alone without schema is potentially ambiguous, as the user may have a different default schema than dbo. This can lead to cache littering, since users with different default schemas cannot share plans for a batch where at least one object is not schema-qualified. Thus, to avoid this, you should always schema-qualify objects in dynamic SQL.

But it is a different matter with static SQL in stored procedures in a database where all objects are in the dbo schema and everything is owned by dbo – which, as noted above, is a very common scenario. There is no ambiguity, as object references are always resolved from the point of view of the procedure owner, not the current user. In this case, I think that adding dbo. in front of all table names is just four-character noise that only serves to make the code more verbose. I have adhered to this practice in this article, exactly to make the examples more concise. I like to stress that if your application actually uses multiple schemas, I think that you should always use two-part notation, also for the objects you put in dbo.

A Security Threat with Ownership Chaining

Before we leave ownership chaining, I like to look at a situation where ownership chaining can open for privilege elevation in combination with some other features.

Say that you have some developers that need to develop stored procedures for reports. Because you have no proper test database with production-like data, you give them permission to work in the production database. Your data is not that sensitive, so there is no issue if they can see the data, but you don't want them to make updates. You create a Reports schema and grant the developers CREATE PROCEDURE on database level (which is the only level where you can grant this permission) and ALTER permission on the Reports schema. You also make them members of the db_datareader role. To test your idea, you run this:

CREATE SCHEMA Reports
go
CREATE ROLE DevRole
CREATE USER MrKite WITHOUT LOGIN
ALTER ROLE DevRole ADD MEMBER MrKite
go
GRANT CREATE PROCEDURE TO DevRole
GRANT ALTER ON Schema::Reports TO DevRole
ALTER ROLE db_datareader ADD MEMBER DevRole

To test that you have the setup right, you first run:

EXECUTE AS USER = 'MrKite'
go
CREATE PROCEDURE testrep AS PRINT 11
go
REVERT

This fails with:

Msg 2760, Level 16, State 1, Procedure testrep, Line 1

The specified schema name "dbo" either does not exist or you do not have permission to use it.

Good! They cannot create procedures in dbo. Next you try:

EXECUTE AS USER = 'MrKite'
go
CREATE PROCEDURE Reports.test_report @id int AS 
   SELECT id, somedata, whodidit FROM dbo.Playtable WHERE id = @id
go
EXEC Reports.test_report 1
go
REVERT

(Because we are now in another schema, it is a good idea to use the dbo prefix to avoid confusion.) However, to your chagrin this fails with:

Msg 229, Level 14, State 5, Procedure test_report, Line 1

The EXECUTE permission was denied on the object 'test_report', database 'Playground', schema 'Reports'.

This happens because no matter who creates an object in a schema, it is the schema owner who becomes the owner of the object, not the user who created the object. This is somewhat confusing, but it has the advantage that when users create objects in the dbo schema, no extra steps are needed to make dbo be the owner the objects.

So in this case, what do you do? Your first thought may be to grant DevRole EXECUTE permission on the Reports schema. But stop! Keep in mind that Mr. Kite and the gang have permissions to create procedures not only to read data, but they also can create procedures to insert, update and delete data in all tables in the Playground database. If they are granted permission to run these procedures, they can thus start manipulating the data in the database. That is, they are able to elevate their permissions beyond what you intended.

No, instead the correct action here is to break the ownership chain and the easiest way to do this is to make DevRole owner of the Reports schema. This is also changes the owner of all the objects within the schema (save those where the owner has been set explicitly):

ALTER AUTHORIZATION ON SCHEMA::Reports TO DevRole
go
EXECUTE AS USER = 'MrKite'
EXEC Reports.test_report 1
go
REVERT

Mr Kite can now run the procedure he wrote. Once the developers are satisfied with their work, you can change the ownership of the individual procedures to dbo or move/copy them to the dbo schema.

While we were able to tighten the hole here, I will have to admit that I am not enthusiastic of granting users permissions that are somewhere between plain read/write/execute permissions and membership in db_owner. The permission system in SQL Server is fairly complex and not always simple to understand. You may lure yourself to believe that you have prevented users from being able to do certain actions, when in fact they only need to do some extra tweak to circumvent your restrictions. My preference for the given scenario is to restore a copy of the production database in a test environment, where the developers can be db_owner all day long. This also has the advantage that they can add test data if they feel that the current data does not cover situations they want to test.

Using Certificate Signing in the Database

We have now looked at ownership chaining and we can see that it works for the main bulk of procedures we write, because most of time all we want to do is to read or write data in some tables and execute other store procedures. But we have also seen that there are situations ownership chaining does not work. In this chapter, we will learn how we can overcome these restrictions by using certificate signing.

To use certificate signing to package extra database permissions in a stored procedure, there is a recipe consisting of four steps:

  1. Create a self-signed certificate.
  2. Sign the procedure with that certificate.
  3. Create a user from that certificate.
  4. Grant that user the permissions needed to run the procedure.

At this point, this may seem like quite a bit of mumbo-jumbo to you, and I will discuss these steps in detail, not only in terms of syntax, but also the deeper meaning behind them. At the same time, we will also arrange so that Michelle can run the procedure check_playdata successfully. However, because there are so many things to explain, it will take a while until I am able to give the full picture.

The script for this chapter is 04_certsigndb.sql. The first batch of this script reads:

SET NOCOUNT, XACT_ABORT ON
USE Playground

Creating the Certificate

First of all, you may wonder what is a certificate? You may have encountered the term in other contexts, but not in SQL Server. A certificate is an asymmetric key with some extra metadata (which is of little interest for this article) around it. An asymmetric key consists of a pair of keys, one public and one private. The public key is just that: public. You typically expose it freely without any means of protection. The private key on the other hand is your secret, and you need to protect it in some way, for instance with a password or encrypting it with another key.

If you look at the syntax for CREATE CERTIFICATE in Books Online, you may be bewildered by all the options, but for this purpose, you will always run the command as in this example:

CREATE CERTIFICATE my_first_cert 
ENCRYPTION BY PASSWORD = 'All You Need Is Love'
WITH SUBJECT = 'This is my first certificate'

That is, you have to give it name, you must specify a password to protect the private key, and because the syntax mandates it, you also have to specify a subject. In a "real" certificate, the subject is supposed to reflect the owner, but you can put in any free text, for instance to indicate what the certificate is used for. Well, almost any free text. There is a format for subjects defined by X.509, and it has some restrictions. If you try:

CREATE CERTIFICATE mysecondcert 
ENCRYPTION BY PASSWORD = 'Eight Days a Week'
WITH SUBJECT = 'Music, Song and Dance'

You get this error message:

Msg 15297, Level 16, State 46, Line 10

The certificate, asymmetric key, or private key data is invalid.

The culprit is the comma. I will have to admit that I have not investigated exactly what is permitted by X.509. But until recently when I tried the above, I have never had any problems with subjects.

Signing the Procedure

Syntax-wise, this is not very difficult:

ADD SIGNATURE TO check_playdata
   BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love'

That is, the command is ADD SIGNATURE and you specify the name of the object to sign. You use the clause BY CERTIFICATE to specify the certificate and since signing requires access to the private key, you need to give the password for the key.

You may ask yourself what all this means. Signing something with a certificate is something which is applicable far beyond SQL Server. For instance, if you send a email, you can sign the mail with your private key. You make your public key available, and the person who receives your mail can use the public key to verify that the contents agrees with the signature. That is, the receiver can be sure that the mail is from you, and that the contents has not been tampered with in transit.

If you work for an ISV who ships a product to many customers, you could opt to sign all your stored procedures, triggers and functions during installation and drop the private key from certificate after installation. You can then use this to verify that customers have not made their own changes to the installation.

But what does this have to do with permissions in SQL Server? That is a question we will need to defer to answer, until we have looked at all steps in the recipe. Here I will briefly discuss some details around the command itself.

You can sign a procedure with more than one certificate.

To sign a procedure, you need to have ALTER permission on the procedure and CONTROL permission on the certificate. In line with the scepticism I expressed earlier of handing out partial elevated permissions, I'd say that in practice this is an activity for members in the db_owner role.

Books Online does not give full details on what you can sign, and I have not made extensive investigations myself. What matters for this article is that you can sign all executable modules, that is, stored procedures, triggers, scalar functions and multi-statement functions. But you cannot sign views and inline table functions, which are not modules you execute. (Rather they are macros that are expanded into the query prior to optimisation.) Curiously enough, you can sign tables.

The view sys.crypt_properties holds information about all signed modules in the database. For a sample query, see the next section.

Creating a User from the Certificate

The next step is to create a user from the certificate. This is done this way:

CREATE USER my_first_cert_user FROM CERTIFICATE my_first_cert

Again, you may ask yourself what this is good for. And admittedly, the certificate user is a bit of a kludge. The SQL Server team wanted a way to tie permissions to certificates, but it seems funny to grant permissions to a certificate, so they introduced this user a bridge between the certificate and the permissions.

You may feel uncomfortable with adding an extra user to your database, but this is quite a special user. If you run

SELECT * FROM sys.database_principals WHERE name = 'my_first_cert_user'

You will see that the type_desc column returns CERTIFICATE_MAPPED_USER. Furthermore, if you try:

EXECUTE AS USER = 'my_first_cert_user'

You get an error message:

Msg 15517, Level 16, State 1, Line 22

Cannot execute as the database principal because the principal "my_first_cert_user" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Also, note that you can only create one user per certificate, so this fails:

CREATE USER my_second_cert_user FROM CERTIFICATE my_first_cert

The error message is somewhat confusing, though:

Msg 15063, Level 16, State 1, Line 24

The login already has an account under a different user name.

Here is a query that lists which procedures (and other modules) that have been signed and by which certificate. It also returns any user created from the certificate:

SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
       c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM   sys.crypt_properties cp
JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT   JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id

Here is the result set from this query, split up over several lines for readability:

Module                  Cert           subject

----------------------- -------------- -----------------------------

[dbo].[check_playdata]  my_first_cert  This is my first certificate

 

Username             class class_desc         major_id

-------------------- ----- ------------------ -----------

my_first_cert_user   1     OBJECT_OR_COLUMN   341576255

 

thumbprint                                   crypt_type crypt_type_desc

-------------------------------------------- ---------- -------------------------

0x46AD9DD1B43D4F78A4533ADB1DFD7D2E2F9B47C4   SPVC       SIGNATURE BY CERTIFICATE

 

crypt_property

-----------------------------------------------------------------------

0x94FCCA5A362D1AE8CFCFECFBE0A97FBEF46A734D1D13CA0D8B9A9C8C75C9515B2....

To permit a legible format of the article, I have truncated the last column, crypt_property, which is the signature itself. As you can see this is a binary value and not human-readable. The thumbprint is like a GUID for a certificate. That is, it uniquely identifies a certificate, not only in SQL Server, but across the planet.

Granting Permissions to the Certificate User

This last step of the recipe is less revolutionary in terms of commands as this is a matter regular permission assignment. for which you use the GRANT statement. It is also possible to add the certificate user to a role with ALTER ROLE ADD MEMBER to package the powers of the role with the stored procedure.

The delicate part is to determine the correct permissions to grant. While you could solve all problems by making the cert user member of db_owner, you should make it a habit of never granting more permissions than you think that are needed. For the procedure check_playdata, the extra permission needed is the humble VIEW DEFINITION on Playtable. Thus:

GRANT VIEW DEFINITION ON Playtable TO my_first_cert_user

Now having executed all four steps in the recipe, Michelle makes a new attempt with check_playdata:

EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT

This time, Michelle sees the data:

id   somedata                              whodidit

---- ------------------------------------- ----------

1    Des mots qui vont très bien ensamble  Michelle

But What Happened?

We have now seen that we could use certificate signing to add the VIEW DEFINITION permission to the procedure check_playdata to get it work as desired. Still, there is one piece missing for us to be able understand what happened. The last piece of the puzzle is found in the view sys.user_token. We will not query this view directly, but we will package it into our own view, called tokeninfo:

CREATE VIEW tokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.user_token

That is, beside the three columns of interest from sys.user_token, we also look at the output from three system functions: original_login() that tells us who originally logged in, SYSTEM_USER which returns the current user on server level and USER which returns the current user on database level. If you run a SELECT from this view while being logged in as sysadmin, you will see something like this:

token_name   type      usage          original_login     SYSTEM_USER        DBUSER

------------ --------- -------------- ------------------ ------------------ -------

dbo          SQL USER  GRANT OR DENY  LIVERPOOL\GMartin  LIVERPOOL\GMartin  dbo

(Instead of LIVERPOOL\GMartin, you will obviously see your own login name. For the output in this article, I am pretending that it is produced when a certain GMartin is logged with integrated security on the domain or workstation LIVERPOOL.)

You may think that this does not tell you something you don't already know, but let's add it to the procedure check_playdata and then run the procedure again as Michelle:

ALTER PROCEDURE check_playdata @id int AS
   SELECT * FROM tokeninfo
   IF object_id('Playtable') IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT

We see this:

token_name   type     usage         original_login     SYSTEM_USER     DBUSER

------------ -------- ------------- ------------------ --------------- --------

Michelle     SQL USER GRANT OR DENY LIVERPOOL\GMartin  S-1-9-3-366...  Michelle

public       ROLE     GRANT OR DENY LIVERPOOL\GMartin  S-1-9-3-366...  Michelle

Playrole     ROLE     GRANT OR DENY LIVERPOOL\GMartin  S-1-9-3-366...  Michelle

 

Playtable has not been created yet. Try again later.

This time we get three rows from the tokeninfo view, and we can now understand what a token is. There are three of them, Michelle, the current user, and then there are public and Playrole, the two roles that Michelle is a member of. GRANT OR DENY in the usage column tells us that the token can contribute with a GRANT or DENY of permissions. The output from the three system functions is not that important at this time, but as they will be later on, I like to comment on them. original_login retains GMartin, while the DBUSER column reports Michelle, as we are impersonating Michelle. SYSTEM_USER reports only a SID, since Michelle is a user WITHOUT LOGIN.

Below the result set, we can tell from the PRINT message that the powers of the certificate have vanished. Why is it so? Recall that the point with signing something with a certificate is to be able to verify that the contents has not changed. We changed the procedure, and thus the signature is no longer valid. In fact, if you run the query against sys.crypt_properties, you will find it now returns an empty result set. That is, every time you change a signed procedure, SQL Server removes the signature.

So let's sign the procedure anew and run as Michelle:

ADD SIGNATURE TO check_playdata
   BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love'
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT

This time we get this output (for brevity, I'm not including the three columns to the right):

token_name           type                        usage

-------------------- --------------------------- --------------

Michelle             SQL USER                    GRANT OR DENY 

public               ROLE                        GRANT OR DENY 

Playrole             ROLE                        GRANT OR DENY 

my_first_cert_user   USER MAPPED TO CERTIFICATE  GRANT OR DENY

 

id          somedata                                 whodidit

----------- ---------------------------------------- ----------

1           Des mot qui vont très bien ensamble      Michelle

We see now that a fourth token has been added, the token of my_first_cert_user, and we can now explain what happens. If all of these three are true:

  1. The procedure is signed with a certificate.
  2. The signature is valid.
  3. There is a user tied to the certificate

Then the token of the certificate user is added to sys.user_token, and it contributes with permissions just like any of the other tokens does. If the certificate user is member of one or more roles, the tokens for these roles are also added. (There are no examples of this in the main article, but you can find this in the first two chapters in the appendix.) In this case, my_first_cert_user contributes with VIEW DEFINITION on Playtable, which is why Michelle can see the data in the table.

We have now acquired understanding of the mechanisms that make certificate signing work, but there are still some more details we need to learn and we will look at them in the next section. I can sense that the reader has some questions about the fact we need to re-sign the procedure every time and how we are supposed to manage all these certificates. Don't worry. I will return to these questions.

The Scope of the Certificate Token

Admittedly, the fact that we could get object_id to work for Michelle without granting her any permission directly on Playtable will have to count as a fairly small victory. Let's see if we can get this to work with something else, for instance with dynamic SQL. We previously looked at this bad procedure:

CREATE PROCEDURE dynamic_playdata @searchstr nvarchar(MAX) AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT * FROM dbo.Playtable
                  WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
   EXEC(@sql)

When we apply the recipe on this procedure we need to determine the permissions needed. Quite obviously, this is a SELECT permission, and since a single table is accessed, all we need is to grant SELECT on this table. Thus we, get:

CREATE CERTIFICATE dynamic_playdata$cert
ENCRYPTION BY PASSWORD = 'While My Guitar Gently Weeps'
WITH SUBJECT = 'GRANT SELECT ON dbo.Playtable'

ADD SIGNATURE TO dynamic_playdata BY CERTIFICATE dynamic_playdata$cert
   WITH PASSWORD = 'While My Guitar Gently Weeps'

CREATE USER dynamic_playdata$certuser FROM CERTIFICATE dynamic_playdata$cert

GRANT SELECT ON dbo.Playtable TO dynamic_playdata$certuser

You may note that this time I have given the certificate and the user a name which is derived from the stored procedure. There is an obvious point in this: you can immediately see what purpose the certificate and the user serve. (In case if you think that the $ character has a special meaning, it has not. It is just another identifier character like A or _, only less commonly used.) I use the subject to state the permissions granted.

The net effect is that the query to display signed procedures directly tells us which stored procedure that are signed and what permissions that are packaged into them. Let's run that query again:

SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
       c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM   sys.crypt_properties cp
JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT   JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id

We see this in the first three columns (I leave out the rest in the interest of brevity):

Module                            Cert                    subject

--------------------------------- ----------------------- ------------------------------

[dbo].[dynamic_playdata]          dynamic_playdata$cert   GRANT SELECT ON dbo.Playtable

[dbo].[check_playdata]            my_first_cert           This is my first certificate

It is now time for Michelle to run this. Recall that last time, Michelle got a permission error.

EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata 'ensamble'
go
REVERT

This time the procedure returns a result set. Thus, we have scored another victory for this technique: we were able package the SELECT permission needed to run the dynamic SQL with the stored procedure. Note here the importance of not granting more permissions than needed: the procedure is still vulnerable to SQL injection, but all a malicious user would be able to do is to wrestle out more data from Playtable than he is supposed to see. Depending on what data the table holds, this can be bad enough, but at least it is far better than what would have been the case if ownership chaining had worked – full access to all tables in the database.

One observation we can make from this feat is that the token of the certificate user is carried on into the dynamic SQL, which, as I said previously, is a nameless stored procedure on its own. This may not seem particularly noteworthy at this point, but let's explore this a little more. Here is a not particularly meaningful stored procedure that displays the indexes on Playtable and Michelle tries to run it:

CREATE PROCEDURE show_playindexes AS
   EXEC sp_helpindex 'Playtable'
go
EXECUTE AS USER = 'Michelle'
EXEC show_playindexes
go
REVERT

We don't really expect ownership chaining to work here, and indeed there is an error message:

Msg 15009, Level 16, State 1, Procedure sp_helpindex, Line 38

The object 'Playtable' does not exist in database 'Playground' or is invalid for this operation.

To see the indexes on a table, you need the permission VIEW DEFINITION on the table. As we already have a certificate for this purpose, we are lazy and reuse it whereupon Michelle makes a second attempt:

ADD SIGNATURE TO show_playindexes BY CERTIFICATE my_first_cert
   WITH PASSWORD = 'All You Need Is Love'
go
EXECUTE AS USER = 'Michelle'
EXEC show_playindexes
go
REVERT

This time Michelle can successfully run the procedure. Thus, we can conclude that the token of the certificate user is carried on into the system procedure.

But what happens if we invoke user-defined objects? We create a stored procedure and a trigger that both return information from the tokeninfo view together with the name of the module:

CREATE PROCEDURE inner_sp AS
   SELECT 'inner_sp' AS wherearewe, *
   FROM   tokeninfo
go
CREATE TRIGGER play_tri ON Playtable AFTER INSERT AS
   SELECT 'In trigger' AS wherearewe, *
   FROM   tokeninfo

Next, we create an outer procedure that invokes these two and also displays the information in tokeninfo.

CREATE PROCEDURE outer_sp @id int, @somedata nvarchar(40) AS
   SELECT 'outer_sp' AS wherearewe, token_name, type, usage
   FROM   tokeninfo
   INSERT Playtable(id, somedata)
      VALUES(@id, @somedata)
   EXEC inner_sp

There is no need to sign outer_sp (or any of the other two) to add extra permissions. However, we want to know how far the scope of the certificate extends, and therefore we sign outer_sp with my_first_cert and then try it as Michelle:

ADD SIGNATURE TO outer_sp BY CERTIFICATE my_first_cert
   WITH PASSWORD = 'All You Need Is Love'
go
EXECUTE AS USER = 'Michelle'
EXEC outer_sp 4, 'Testing the scope'
go
REVERT

This is the output (only including the columns from sys.user_token for brevity):

wherearewe token_name           type                        usage

---------- -------------------- --------------------------- -------------

outer_sp   Michelle             SQL USER                    GRANT OR DENY

outer_sp   public               ROLE                        GRANT OR DENY

outer_sp   Playrole             ROLE                        GRANT OR DENY

outer_sp   my_first_cert_user   USER MAPPED TO CERTIFICATE  GRANT OR DENY

 

wherearewe token_name           type                        usage

---------- -------------------- --------------------------- -------------

In trigger Michelle             SQL USER                    GRANT OR DENY

In trigger public               ROLE                        GRANT OR DENY

In trigger Playrole             ROLE                        GRANT OR DENY

 

wherearewe token_name           type                        usage

---------- -------------------- --------------------------- -------------

inner_sp   Michelle             SQL USER                    GRANT OR DENY

inner_sp   public               ROLE                        GRANT OR DENY

inner_sp   Playrole             ROLE                        GRANT OR DENY

That is, we can see that the token of the certificate user is there when we enter outer_sp, but when we enter the trigger or the inner procedure, it has been removed!

At first this may seem inconsistent. The certificate is carried on into some scopes, but not into others. However, there is a point in this. While not very common, it could be the case that an inner procedure or a trigger is designed to explicitly check whether the user has a certain permission. If the token from the certificate user would be carried on from the caller, the inner procedure would be lured. (While this may seem far-fetched, I make use of this in the chapter Loading CLR Objects in the appendix.) If you want the powers of the certificate in the inner procedure as well, you need to sign that too. On the other hand, you cannot sign a batch of dynamic SQL, nor can you sign a system procedure, so in this case it is very practical that the certificate token is retained.

Before we go on, we drop the trigger:

DROP TRIGGER play_tri

Certificate Signing and DENY

There is one more thing we need to look at to get the full picture. We explicitly DENY Michelle access on the Playtable:

DENY SELECT, UPDATE, DELETE, INSERT ON Playtable TO Michelle

Michelle the tries to run both add_playdata to add a row and dynamic_playdata to find the new row:

EXECUTE AS USER = 'Michelle'
go
EXEC add_playdata 5, 'Crawled off to sleep in the bath'
EXEC dynamic_playdata 'bath'
go
REVERT

This results in this output:

id   somedata                           whodidit

---- ---------------------------------- ---------

5    Crawled off to sleep in the bath   Michelle

 

Msg 229, Level 14, State 5, Line 131

The SELECT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.

We can tell that add_playdata ran successfully, despite the DENY. As ownership chaining is in play, no permissions are checked, neither granted nor denied. However, dynamic_playdata now fails despite that it is signed by the certificate. Remember that the way certificate signing works is that the token of the certificate user is added to the other tokens and in this way contributes with permission on equal footing with the other tokens. Previously, the only permission in the mix was GRANT SELECT given to the certificate user. But now the situation has changed, as Michelle's token includes DENY SELECT on Playtable, and DENY always takes precedence over GRANT. Thus, you can never use certificate signing to overcome permission problems that are due to DENY (or membership in a role like db_denydatawriter).

Overall, DENY is something you should use avoid as much as possible, exactly because of this behaviour. And there should rarely be any need for DENY if you keep in mind that by default users have no permissions at all in the database.

At this point we revoke the DENY for Michelle, since we only added it for this example:

REVOKE SELECT, UPDATE, DELETE, INSERT ON Playtable FROM Michelle

Before we move on to the next section, let us summarise what we have learnt:

Certificate Management

At this point I can sense readers who are thinking: Yeah, while this is nice and cosy, it has no practical usage, because it would be a nightmare to manage all these certificates, not talking about all the passwords. And having to re-sign the procedure after each change, what a pain! This is the section where we will address these concerns.

So far we have used two certificates, and we have used them in two different ways. One certificate, dynamic_playdata$cert, has a name which is derived from the procedure it signs, whereas we have used my_first_cert to sign three different procedures. This sort of indicates two main strategies: one certificate for every procedure that needs special powers, or a few general certificates that you use when needed.

In my opinion this is a simple choice. The latter method will indeed lead to a lot of work to keep track of certificates, passwords and what permissions they go along with. I only took the freedom to reuse my_first_cert a few times, because we had not yet learnt to automate the creation of certificates and signing of procedures. Tailoring a certificate for every procedure where you want to package extra permissions is a much better model, exactly because it is a lot easier to automate. In this model, the password is something you never have to see, but you can throw away. Why, you will see in an instant.

Here is the basic idea, expressed in T‑SQL, although this is nothing that compiles and runs – I use variables in a lot of places where it is not permitted. (As this code does not run, it is not included in the script for this chapter).

DECLARE @certname sysname = @spname + '$cert',
        @username sysname = @spname + '$certuser',
        @password char(37) = convert(char(36), newid()) + 'a'

DROP SIGNATURE IF EXISTS FROM @spname BY CERTIFICATE @certname
DROP USER IF EXISTS @username
DROP CERTIFICATE IF EXISTS @certname

CREATE CERTIFICATE @certname
ENCRYPTION BY PASSWORD = @password
WITH SUBJECT = 'GRANT SELECT ON Playtable'

ADD SIGNATURE TO @spname BY CERTIFICATE @certname
   WITH PASSWORD = @password

CREATE USER @username FROM CERTIFICATE @certname

GRANT SELECT ON Playtable TO @username

That is, we first generate names for the certificate and user from the name of the procedure. Next we drop any existing signature from the procedure, and then we drop the user and the certificate. Then we create a new certificate. It has the same name, but it is a new certificate, with different keys and a different thumbprint than the previous one. For the password we use a random GUID with a lowercase character added to be sure that we pass the complexity requirements in Windows. (Three out of the four categories uppercase, lowercase, digits, and punctuation). In the subject we put the permissions granted, so that we can easily see this when we run the query we have used earlier. Then we sign the procedure, create the user and grant the permissions. Since we create a new certificate every time, we only need the password twice: once to create the certificate and once to sign. Then we can forget it.

As noted, the above does not compile, but we need to use dynamic SQL to create the commands. I have packaged this in a stored procedure, GrantPermsToSP. You find the full code for this procedure in the file GrantPermsToSP.sql. As the procedure as such is more of an exercise in writing good dynamic SQL, I have not included the full code in the article, but here I will only present the interface and some examples. The procedure runs on SQL 2008 and higher, but because the use of a table-valued parameter, it does not run on SQL 2005.

Since this is a management procedure and not part of any application, I have a separate schema for it:

CREATE SCHEMA Management

You may remember that I earlier I talked of how schemas can be security boundaries. Recall that we granted EXECUTE rights to Playrole on the dbo schema. Thus, by using this model, we put this procedure out of reach of the plain users.

To be able to specify multiple permissions, the procedure takes a table-valued parameter, and for this we need a table type:

CREATE TYPE Management.Permission_list AS 
       TABLE(perm nvarchar(400) PRIMARY KEY)

Here the signature of the procedure:

CREATE PROCEDURE Management.GrantPermsToSP 
                 @spname       nvarchar(520),
                 @permissions  Management.Permission_list READONLY,
                 @debug        bit = 1 AS
@spname
The name of the procedure, with or without schema specified. (You cannot specify a procedure in a different database.)
@permissions
One or more permissions you want to grant. You can also specify a role that exists in the database, and the procedure will detect that and instead and the certificate user to the role. Note, though, that specifying a role will fail on SQL 2008, since the procedure uses ALTER ROLE. There is a commented call to sp_addrolemember that you can activate for SQL 2008.
@debug
Set to 1 if you want to see the generated SQL. Very much recommended until you have grasped what the procedure is doing, and an absolute must if you get an error.

One procedure we looked at earlier was truncate_playdata which permits the user to empty the table, but since it uses TRUNCATE TABLE, Michelle was not able to run this procedure. This requires ALTER permission on the table. Let's now use GrantPermsToSP to arrange for that:

DECLARE @perms Management.Permission_list
INSERT @perms (perm) VALUES('ALTER ON Playtable')
EXEC Management.GrantPermsToSP 'truncate_playdata', @perms, @debug = 1

Since we run with the debug flag set, we see an output like this (the password will be different each time):

CREATE CERTIFICATE [truncate_playdata$cert]
       ENCRYPTION BY PASSWORD = '46D92ECF-B2E8-4F7D-BD34-DF5A60B6DDE9a'
       WITH SUBJECT = 'GRANT ALTER ON Playtable'
ADD SIGNATURE TO [dbo].[truncate_playdata]
       BY CERTIFICATE [truncate_playdata$cert]
       WITH PASSWORD = '46D92ECF-B2E8-4F7D-BD34-DF5A60B6DDE9a'
CREATE USER [truncate_playdata$certuser] 
                  FROM CERTIFICATE [truncate_playdata$cert]
GRANT ALTER ON Playtable TO [truncate_playdata$certuser]

That is, this is what I suggested above, except that are no DROP statements, as the procedure was not signed previously. If you run the same thing a second time, the DROP statements appear and the password is different. (In passing: this is useful, if you find that the permissions you tried in your first attempt were not the best ones.)

DROP SIGNATURE FROM [dbo].[truncate_playdata] BY CERTIFICATE [truncate_playdata$cert]
DROP USER [truncate_playdata$certuser]
DROP CERTIFICATE [truncate_playdata$cert]
CREATE CERTIFICATE [truncate_playdata$cert]
       ENCRYPTION BY PASSWORD = '476AED96-FB50-4660-8771-E63EE88F98E5a'
       WITH SUBJECT = 'GRANT ALTER ON Playtable'
ADD SIGNATURE TO [dbo].[truncate_playdata]
       BY CERTIFICATE [truncate_playdata$cert]
       WITH PASSWORD = '476AED96-FB50-4660-8771-E63EE88F98E5a'
CREATE USER [truncate_playdata$certuser] 
                  FROM CERTIFICATE [truncate_playdata$cert]
GRANT ALTER ON Playtable TO [truncate_playdata$certuser]

To prove the concept, Michelle gives it a whirl:

EXECUTE AS USER = 'Michelle'
EXEC truncate_playdata
go
REVERT
SELECT * FROM Playtable

This completes without error, and we can see that the table is now empty.

Here are some more details on how the procedure works. Say that you find that a procedure was signed in error, and no permissions should be granted to it. In this case, you simply call the procedure without passing the @permissions parameter. (Recall that a table-valued parameter always has an implicit default value of an empty table.)

EXEC Management.GrantPermsToSP 'truncate_playdata', @debug = 1

This is the output:

DROP SIGNATURE FROM [dbo].[truncate_playdata] BY CERTIFICATE [truncate_playdata$cert]
DROP USER [truncate_playdata$certuser]
DROP CERTIFICATE [truncate_playdata$cert]
Procedure not signed - no permissions given

Signature, user and certificate are dropped, and there is a message to inform you that the procedure is not signed this time.

If the procedure is signed by other certificates of which the names are not derived from the procedure, these are not touched. You may recall that show_playindexes is already signed by my_first_cert. Here we add some permissions that serve no other purposes than to test the signing procedure:

DECLARE @perms Management.Permission_list
INSERT @perms (perm)
   VALUES('INSERT, SELECT, DELETE, UPDATE ON Playtable'),
         ('CREATE PROCEDURE'),
         ('IMPERSONATE ON USER::Michelle')
EXEC Management.GrantPermsToSP 'dbo.show_playindexes', @perms, @debug = 1

The output is (with one line broken for legibility):

CREATE CERTIFICATE [show_playindexes$cert]
       ENCRYPTION BY PASSWORD = '770260F7-CE1B-466E-943C-1F8BEE7485E2a'
       WITH SUBJECT = 'GRANT CREATE PROCEDURE - IMPERSONATE ON USER::Michelle -
                       INSERT  SELECT  DELETE  UPDATE ON Playtable'
ADD SIGNATURE TO [dbo].[show_playindexes]
       BY CERTIFICATE [show_playindexes$cert]
       WITH PASSWORD = '770260F7-CE1B-466E-943C-1F8BEE7485E2a'
CREATE USER [show_playindexes$certuser] 
                  FROM CERTIFICATE [show_playindexes$cert]
GRANT CREATE PROCEDURE TO [show_playindexes$certuser]
GRANT IMPERSONATE ON USER::Michelle TO [show_playindexes$certuser]
GRANT INSERT, SELECT, DELETE, UPDATE ON Playtable TO [show_playindexes$certuser]

As you use can see, my_first_cert does not appear in the output and is thus unaffected by the operation. You can also see how multiple permissions are handled in the subject. They are separated by dashes. (The line break is not there in the subject, but only to fit the article format.) Also, since commas can cause the subject to be invalid, the procedure removes these.

The name of the certificate and the user is constructed from the procedure name alone when the procedure is in the dbo schema, but if the procedure is in a different other schema, the schema name is included as well. For instance, if you have a procedure my_sp in the schema myschema, the certificate would be named myschema.my_sp$cert. (Note that the dot here is part of the name; certificates and users are not bound to schemas themselves.)

How you would actually use GrantPermsToSP depends a little on your circumstances. If you are in an environment where your group develops an application which is under version control, it seems natural to me that the call to GrantPermsToSP would be in the deployment script for the stored procedures that need extra permissions. Some tools may be a little squared and don't agree with this, and in such case you may have to put these calls in a some post-deployment script. (Readers who use SSDT may hope for advice, but since I've only played with SSDT and never used it for real, I will have to pass on that one.) The key property from a security perspective is that in this type of environment, developers are generally trusted to hand out permissions that should be in the application, even if they do not have permission on the production database themselves.

Other environments may be laxer when it comes to version control, but are more strict on security. For instance, there may be users who have permission to write stored procedures in one schema (which is not owned by dbo). They have read and write access to the dbo schema, but they have no permissions to change tables (which are in dbo), add users etc. In this case, they will not be able to run GrantPermsToSP, and nor do you want them to. You want to review their code, before you agree to add elevated permissions to their procedures. In this type of scenario, the fact that the signature disappears after every change is extremely important. When you have a signed a procedure, the developers cannot go back alter the procedure to do something you would not agree to. Every time a developer makes a change to a privileged procedure, you can request to review the code anew before you sign.

Variations on the Theme

This section contains some supplementary information that is not essential for understanding of the technique. Feel free to skip it, if you like.

Certificates without Passwords

If you create a database master key and have it open when you create your certificate, you don't need to specify a password when you create the certificate, as the database master key protects the private key of the certificate. For the same reason, you don't need any password when you sign the procedure.

While this makes things a little simpler, I have been reluctant to use this. Given that the password can be handled entirely inside GrantPermsToSP, the password is not a big deal in the first place. I will have to admit that I'm foggy on when the database master key is open and when it is not, but if you copy the database to another server it may not be open, unless the two instances have the same service master key. (The service master key protects the database master key.) Thus, there is a risk is that you find yourself confused when something that you are used to work, all of a sudden does not.

Using Asymmetric Keys

Instead of certificates, you can use asymmetric keys. This is simpler so far that you don't have to specify a subject, and if you also have a database master key, you only need to say

CREATE ASYMMETRIC KEY key1 WITH ALGORITHM = RSA_2048

While you don't have to specify subject or password, you must specify an algorithm.

In this article, I have opted to work with certificates only, despite the small hassle with the subject. The reason is that certificates can (relatively) easily be exported to other databases, while this is not all simple with asymmetric keys. This is something we need to do when signing procedures for server-level access, which we will look into in the next chapter.

Countersignatures

When you sign a procedure, you can insert the keyword COUNTER:

ADD COUNTER SIGNATURE TO ...

When you countersign a procedure, the signature has no effect if the procedure is called directly. However, if the procedure is called by an outer procedure which is signed with the same certificate, the token of the certificate user is not removed when the countersigned procedure is entered, but lingers on.

This is sort of a feature that looks for a problem to solve, but imagine this: you have a search procedure that uses dynamic SQL. However, the search procedure itself is too general and exposes too much data. Instead users should call an outer procedure which adds extra filters depending on what the user has permission to see. In this case you can sign the outer procedure with a certificate of which the user has been granted SELECT permission on the tables in question, whereas you only countersign the inner procedure.

In the appendix, the chapter Letting Users Start Specific Jobs includes a solution that uses countersignatures.

Adding a Pre-Made Signature

This is something that goes a little beyond the main subject of this article, but I still find it worth a quick mention. You can say:

ADD SIGNATURE TO some_sp BY CERTIFICATE some_cert WITH SIGNATURE = 0x.....

where the BLOB that follows WITH SIGNATURE is the signature itself. When you do this, only the public key of the certificate needs to be present.

One situation where this can be useful is when you develop an application which you ship to customers and you want to be able to detect whether the customers make changes to the code or the tables you shipped to them. In your database at home, you sign all your procedures (and everything else you can sign and want to detect tampering of). For this purpose, you would probably use the same certificate for all objects. When you build the installation kit, you only include the public key of the certificate (you will learn later how you can extract the certificate from the database), and you get the signatures from sys.crypt_properties. If the customer changes an object, the signature disappears. They can drop your certificate and create a new with the same name and sign the procedure. But when you make an audit, you would find that the signature and the thumbprint are not the same.

Note that if you also use certificate signing to package permissions, you could still use GrantPermsToSP in your installation kit. The certificate you use to detect tampering should not be mixed with certificates you use to package permissions.

In the topic for ADD SIGNATURE in Books Online, there is an example that employs this option.

DDL triggers

You cannot sign DDL triggers, be that on database or server level. There is simply a disconnect in the syntax – there is no module class that matches DDL triggers. This is not a major obstacle. You can pass the contents from eventdata() to a stored procedure which you have signed. For a server-level trigger there is another alternative that we will look at in the section Server-level Objects later. Nevertheless, if you feel that this is a shortcoming that requires rectification, there is a feedback item from Solomon Rutzky you can vote for.

Using Certificates to Package Server-Level Permissions

We will now look at how we can package server-level permissions in stored procedures with help of certificate signing. We will first learn how to do this for procedures stored in the master database; this is not too different from how things work on database level. We then move on to look at what is needed for stored procedures in user databases, something that requires more steps and considerations. As with using certificates on database level, I will offer a way to automate the process.

The script for this chapter is 05_certsignserver.sql. I would like to remind you that you should be careful not to work on any important server, since we will create logins and other objects on server level in this chapter.

A Scenario

Before we look at the actual technique, let me first introduce a scenario that we will work with. Assume that you are a DBA on a server with many databases that generally should not know about each other. Each database have their own set of power users who typically have no server-level permissions.

The power users have a need to see information in DMVs related to their database. For instance, they may need to see which users that are connected to their database. The query for this purpose is a simple one:

SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()

The crux is that to see other sessions than your own in sys.dm_exec_sessions, you need the server-level permission VIEW SERVER STATE. But if you would grant the power users that permission, they would be able to see sessions in all databases and lot more things they are not entitled to. In this chapter, we will learn how we can package this permission in a stored procedure, so that the power user can see what they need to see, but no more.

Note: the column database_id was added to sys.dm_exec_sessions in SQL 2012. On SQL 2008, you have to use sys.sysprocesses.dbid instead, but apart from that the problem is the same with regards to permissions.

Before we can start writing stored procedures, we need a setup. Here is a script that creates the server role PowerUsers and a login for a certain Sgt Pepper, the power user of the Playground database. We add Sgt Pepper to PowerUsers, and then we move over to Playground where we make him member of the db_owner role. (Note: server roles were introduced in SQL 2012 and are not available in SQL 2008.) While we are at it, we also create a view logintokeninfo in the master and Playground databases for diagnostic purposes. It is akin to the tokeninfo view we looked at earlier, but logintokeninfo displays tokens on server level taken from sys.login_token.

SET XACT_ABORT, NOCOUNT ON
USE master
go
CREATE SERVER ROLE PowerUsers
CREATE LOGIN SgtPepper WITH PASSWORD = 'Lonely Hearts Club Band'
ALTER SERVER ROLE PowerUsers ADD MEMBER SgtPepper
go
CREATE VIEW logintokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.login_token
go
USE Playground
go
CREATE USER SgtPepper
ALTER ROLE db_owner ADD MEMBER SgtPepper
go
CREATE VIEW logintokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.login_token

Signing Stored Procedures in the master Database

We will first look at solving the problem for the power users with help of a stored procedure in the master database which makes use of an undocumented feature in SQL Server:

USE master
go
CREATE PROCEDURE sp_ShowSessions AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)

Because the procedure resides in master and the name starts with sp_, the procedure can be accessed from any database but it will execute in the context of the database it was invoked from. Thus, this is what db_id() will return, and the procedure will only display users connected to the current database. As I said, this is undocumented and thus unsupported, and it could break in any release. Particularly, Microsoft could ship their own sp_ShowSessions with the next version of SQL Server, which would trump what we have put in master. Use at your own risk.

The procedure starts off with returning the information from logintokeninfo, which is purely for diagnostic purposes. It then goes on to check whether the user is entitled to see the information. The user must be a member of the server role PowerUsers and also be db_owner in the current database. This latter check is needed in case the user would be a plain user in some other database where he has no right to see connected users. The procedure also permits a user who has the permission CONTROL SERVER to run the query, so that sysadmin can run the procedure. (Why checking for CONTROL SERVER and not VIEW SERVER STATE? Let's talk about that later.) If none of these conditions are fulfilled, we raise an error.

You may find it funny that we do the permission check inside the procedure. Why not simply only grant access to PowerUsers to run this procedure? The problem is that PowerUsers is a server principal, but EXECUTE permission is something you grant to database principals. And what database principals are there in master? Yes, you could mirror PowerUsers with a database role in master, but you would also have to add all the power people as users in master, and who wants users in master? Not me. So, there is really only one option: public.

GRANT EXECUTE ON sp_ShowSessions TO public

And since everyone can invoke the procedure, the procedure must perform its own permission check.

Let us now test the procedure, both as ourselves and as Sgt Pepper. Note that since we are making server-level access, we must use EXECUTE AS LOGIN to impersonate Sgt Pepper, as EXECUTE AS USER works inside the database only.

USE Playground
go
EXEC sp_ShowSessions
EXECUTE AS LOGIN = 'SgtPepper'
EXEC sp_ShowSessions
go
REVERT

When I run this, I get an output akin to the below. For space reasons, I have deleted the SYSTEM_USER column from the first result set, and I only show the first three columns from sys.dm_exec_sessions:

token_name            type             usage           original_login     DBUSER

--------------------- ---------------- --------------- ------------------ ------

LIVERPOOL\GMartin     WINDOWS LOGIN    GRANT OR DENY   LIVERPOOL\GMartin  dbo

public                SERVER ROLE      GRANT OR DENY   LIVERPOOL\GMartin  dbo

sysadmin              SERVER ROLE      GRANT OR DENY   LIVERPOOL\GMartin  dbo

...

 

session_id login_time              host_name

---------- ----------------------- -----------

51         2017-07-27 21:42:16.740 LIVERPOOL

52         2017-07-23 21:13:38.073 LIVERPOOL

53         2017-07-24 21:56:57.000 LIVERPOOL

 

token_name  type         usage          original_login     SYSTEM_USER  DBUSER

----------- ------------ -------------- ------------------ ------------ ------

SgtPepper   SQL LOGIN    GRANT OR DENY  LIVERPOOL\GMartin  SgtPepper    guest

public      SERVER ROLE  GRANT OR DENY  LIVERPOOL\GMartin  SgtPepper    guest

PowerUsers  SERVER ROLE  GRANT OR DENY  LIVERPOOL\GMartin  SgtPepper    guest

 

session_id login_time              host_name

---------- ----------------------- -----------

51         2017-07-27 21:42:16.740 LIVERPOOL

I have abbreviated the first result set – when logged as sysadmin through Windows authentication GMartin have no less than 17 login tokens. On the particular occasion, GMartin had three query windows open to the Playground database. When running the procedure as Sgt Pepper, you can see three tokens: SgtPepper for the login itself and then there are the two server roles public and PowerUsers. The column original_login reveals that GMartin was the person who actually had logged in, while SYSTEM_USER returns SgtPepper since we are impersonating him. Somewhat unexpectedly, DBUSER reports guest and not SgtPepper. Apparently the USER function is resolved against the master database where the stored procedure resides. (As I said, we are using an undocumented and unsupported feature.) Because Sgt Pepper has no server-level permission, the query against sys.dm_excec_sessions returns a single row for the current session.

The recipe to package server-level permissions in a stored procedure in master is very similar to what we used for database permissions:

  1. Create a self-signed certificate in master.
  2. Sign the procedure with that certificate.
  3. Create a login from that certificate.
  4. Grant that login the permissions needed to run the procedure.

That is, rather than creating a database user, we need to create a server login to be able to tie the certificate to the permission, since we are working with server permissions. And while it may sound scary to create extra server logins, have no fear. Just like the certificate user, the certificate login is nothing that actually can log in or execute.

Here is a script to perform the four steps of the recipe. I have added a SELECT from sys.server_principals, so that you can verify that this is not a regular login (check the column type_desc). Observe that we need to move back to master:

USE master
go
CREATE CERTIFICATE ShowSessions$cert
ENCRYPTION BY PASSWORD = 'Magical Mystery Tour'
WITH SUBJECT = 'GRANT VIEW SERVER STATE'
go
ADD SIGNATURE TO sp_ShowSessions BY CERTIFICATE ShowSessions$cert
    WITH PASSWORD = 'Magical Mystery Tour'
go
CREATE LOGIN ShowSessions$certlogin FROM CERTIFICATE ShowSessions$cert
SELECT * FROM sys.server_principals WHERE name = 'ShowSessions$certlogin'
go
GRANT VIEW SERVER STATE TO ShowSessions$certlogin

Sgt Pepper now makes a second attempt to run sp_ShowSessions:

USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC sp_ShowSessions
go
REVERT

I got this output (for space reasons, I'm only including the first three columns of each result set):

token_name              type                        usage

----------------------- --------------------------- --------------

SgtPepper               SQL LOGIN                   GRANT OR DENY

public                  SERVER ROLE                 GRANT OR DENY

PowerUsers              SERVER ROLE                 GRANT OR DENY

ShowSessions$certlogin  LOGIN MAPPED TO CERTIFICATE GRANT OR DENY

ShowSessions$cert       CERTIFICATE                 GRANT OR DENY

 

session_id login_time              host_name

---------- ----------------------- -----------

51         2017-07-27 21:42:16.740 LIVERPOOL

52         2017-07-23 21:13:38.073 LIVERPOOL

53         2017-07-24 21:56:57.000 LIVERPOOL

In the lower result set, Sgt Pepper now sees the same three sessions as we did when we ran the procedure as ourselves. If we turn to the upper result set, we see that there are now five login tokens. The certificate login is there, and this is the token that contributes with the permission VIEW SERVER STATE. And this explains why we check for the permission CONTROL SERVER in the procedure. Would we check for VIEW SERVER STATE, has_perms_by_name would always return 1, since this permission is present through the certificate.

For reasons unknown to me, the certificate itself appears among the tokens, which is different from what we saw on database level. I don't think there is any practical implication with this.

Server-level Permissions in a User Database

We had to open a case with Microsoft about something, and they found out about our sp_ShowSessions and they told us in no uncertain terms that it is undocumented and unsupported, so we decided to play by the rules and instead deploy a procedure ShowSessions in every database:

USE Playground
go
CREATE PROCEDURE Management.ShowSessions AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)

We keep the check on that the user is member of PowerUsers and db_owner, because we still want to control who can run the procedure. We deploy it to the Management schema, so that plain users in Playrole will not have EXECUTE permission on the procedure.

If you want to package server-level permissions in a stored procedure (or some other module) in a user database, there are more steps in the recipe, because the certificate must be both in master (as this is where the certificate login is defined and the permission is granted) and in the user database (so that we can sign the procedure). There are now seven steps:

  1. Create the certificate in master.
  2. Create a login from the certificate.
  3. Grant the certificate login the required permissions.
  4. Export the certificate.
  5. Import the certificate in the user database.
  6. Sign the procedure with the certificate.
  7. Optionally: drop the private key.

That is, the additional task is primarily to copy the certificate from master to the user database. This can be done in two different ways:

  1. Retrieve the binary representation of the keys and import the certificate with CREATE CERTIFICATE FROM BINARY. This requires SQL 2012 or later.
  2. Use BACKUP CERTIFICATE + CREATE CERTIFICATE FROM FILE. This works also on SQL 2005 and SQL 2008.

As long as you are on SQL 2012 or higher, the first method is preferred, so we will start with that one. You use the function certencoded to get the public key and to get the private key there is certprivatekey. You use their output to create the certificate in the user database with the clause FROM BINARY. This script illustrates the idea:

USE master
go
DECLARE @public_key varbinary(MAX) =
            certencoded(cert_id('ShowSessions$cert')),
        @private_key varbinary(MAX) =
            certprivatekey(cert_id('ShowSessions$cert'),
                           'Magical Mystery Tour',
                           'Magical Mystery Tour')

USE Playground

CREATE CERTIFICATE ShowSessions$cert
FROM BINARY = @public_key
WITH PRIVATE KEY
   (BINARY = @private_key,
    DECRYPTION BY PASSWORD = 'Magical Mystery Tour',
    ENCRYPTION BY PASSWORD = 'Magical Mystery Tour')

The two functions certencoded and certprivatekey accepts an id of a certificate as input; you can't pass the name. The id can however easily be retrieved with the function cert_id. When it comes to certprivatekey, you see that we have to specify the password twice. Or more precisely, we have to specify two passwords. We first need to specify the password under which it is protected in master. We then need to give a new password, to have it protected in the variable. That may seem like an overkill, but SQL Server does not know where we will write this data, so it insists on a password. To keep it simple, I reuse the initial password, but if I wanted to I could have chosen to have a new password for the variable. (If you consider doing this, you should know that the new password actually comes before the old one.)

We then move over to the user database and use CREATE CERTIFICATE FROM BINARY to import the certificate. Again, we need to specify two passwords: one to retrieve the private key from the variable and one to protect it in the user database. As before, I reuse the original password, but you could use a different password in the user database if you so fancy. To keep things simple, I use the same name for the certificate in Playground as I used in master, but what if I wanted to, I could use a new name. As noted previously, what identifies the certificate is the thumbprint.

There is however a slight problem with the above as testified if you try to run it:

Msg 102, Level 15, State 1, Line 79

Incorrect syntax near '@public_key'.

Msg 319, Level 15, State 1, Line 80

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

You see, CREATE CERTIFICATE FROM BINARY does not accept a variable for the keys – they must be given as binary literals. (Why? The only good reason I can think of is that they were short on time, and never came around to implement variable support.) Thus, the above must be modified to use dynamic SQL:

USE master
go
DECLARE @public_key varbinary(MAX) =
            certencoded(cert_id('ShowSessions$cert')),
        @private_key varbinary(MAX) =
            certprivatekey(cert_id('ShowSessions$cert'),
                           'Magical Mystery Tour',
                           'Magical Mystery Tour'),
        @sql nvarchar(MAX)

--SELECT @public_key, @private_key

SELECT @sql =
   'CREATE CERTIFICATE ShowSessions$cert
    FROM BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
    WITH PRIVATE KEY
        (BINARY = ' + convert(varchar(MAX), @private_key, 1) + ',
         DECRYPTION BY PASSWORD = ''Magical Mystery Tour'',
         ENCRYPTION BY PASSWORD = ''Magical Mystery Tour'')'

PRINT convert(varchar(MAX), @sql)

EXEC Playground..sp_executesql @sql

Note here that rather than saying USE Playground, I make use of the fact that I can run a batch of dynamic SQL in a different database, by prefixing sp_executesql with that database. Also note the commented SELECT statement. If something goes wrong when retrieving the keys, we will have no use of the PRINT statement, because @sql will be NULL, since there is a NULL value in the concatenation. But the SELECT can help you to determine which key you did not get.

Note: the reason for the convert in the PRINT statement is that PRINT only returns the first 8000 bytes in the string, and on SQL 2016 and later, I found that the generated string is slightly more than 4000 characters long. Since I wanted you to be able to see the full SQL string, I threw in the convert.

When you run the above, you see an output like this:

CREATE CERTIFICATE ShowSessions$cert
    FROM BINARY = 0x308201C730820130A003020...
    WITH PRIVATE KEY
        (BINARY = 0x1EF1B5B00000000001000000010000001000000054....
         DECRYPTION BY PASSWORD = 'Magical Mystery Tour',
         ENCRYPTION BY PASSWORD = 'Magical Mystery Tour')

Note: You will only see all of the above if you are on

Now that the certificate is imported, we can sign ShowSessions:

USE Playground
go
ADD SIGNATURE TO Management.ShowSessions BY CERTIFICATE ShowSessions$cert
   WITH PASSWORD = 'Magical Mystery Tour'

Sgt Pepper gives it a go:

EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT

The output is the same as in the end of the last session. That is, there are five login tokens, and the Sergeant sees all sessions connected to Playground.

Because we will look at the second method, we remove the signature and the certificate at this point.

DROP SIGNATURE FROM Management.ShowSessions BY CERTIFICATE ShowSessions$cert
DROP CERTIFICATE ShowSessions$cert

The chief reason that you would use the second method is that you are on SQL 2008 or earlier where CREATE CERTIFICATE FROM BINARY is not available. When you use BACKUP CERTIFICATE, there is one more step you need to carry out, as seen in the script below:

USE master
go
BACKUP CERTIFICATE ShowSessions$cert TO FILE = 'C:\temp\certexport.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\certexport.pvk',
                  DECRYPTION BY PASSWORD = 'Magical Mystery Tour',
                  ENCRYPTION BY PASSWORD = 'Magical Mystery Tour')
go
USE Playground
go
CREATE CERTIFICATE ShowSessions$cert FROM FILE = 'C:\temp\certexport.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\certexport.pvk',
                  DECRYPTION BY PASSWORD = 'Magical Mystery Tour',
                  ENCRYPTION BY PASSWORD = 'Magical Mystery Tour')
EXEC xp_cmdshell 'DEL C:\temp\certexport.*'

The command BACKUP CERTIFICATE writes the two keys of the certificate to disk. For the private key we need to give the password to retrieve it, and we need to specify a password to protect it in the file on disk. We move over to Playground where we import the certificate from the file, and again we need to specify two passwords for the private key. One to retrieve it from the file, and one to protect it in the database. Here I have used the same password in all three places, but as previously you could use three different ones.

Lastly, I delete the files from disk, and in the example I rely on that xp_cmdshell is enabled. This is dubious, as best practice is to keep xp_cmdshell disabled. But you must somehow make sure that those files are deleted. This is important for two reasons. One is that if you don't remove the file, BACKUP CERTIFICATE will fail the next time. That is, it errors out, if any of the files you specify already exists. You may note that in the example, I have used quite a generic name for the files, and you may think that I should have used a name which is specific to the certificate and also include date and time to avoid collisions in the future. However, the generic name is on purpose, to really force you to delete the file. While the risk is small, Sgt Pepper could somehow learn the name of the file and the password. He could then import the certificate into the database, change the procedure to his liking and then sign the procedure anew without your involvement.

You may object that the certificate is already there, so all Sgt Pepper only needs to do is to crack is the password. But recall that last optional step in the recipe. This time when we sign the procedure, we apply that step as well:

ADD SIGNATURE TO Management.ShowSessions BY CERTIFICATE ShowSessions$cert
   WITH PASSWORD = 'Magical Mystery Tour'
ALTER CERTIFICATE ShowSessions$cert REMOVE PRIVATE KEY

That is, once the procedure is signed, we drop the private key from the certificate. So even if Sgt Pepper was looking over your shoulders as you were typing the password, that does not matter, as the certificate can no longer be used for signing, only to validate that the signature is OK. The Sergeant runs ShowSessions:

EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT

He can see all sessions connected to Playground, despite that the private key is gone. While I did not do this in this example, you could also remove the private key from the certificate in the master database. You may wonder if this is something that is specific to server-level permissions, since I did not discuss removing the private key in the previous chapter. The answer is that there is no difference. I only find it more compelling to remove the private key when it is imported into another database. I did not discuss this in the previous chapter since it introduced so many new things anyway. But there is absolutely nothing wrong with removing the private key from certificates used for database-level permissions as well.

I like to end this section with revisiting two observations we have made earlier. The first is that you need to re-sign the procedure after every change. Do you see how important this is in this case? Sgt Pepper is db_owner and can change any procedure in Playground, and if the powers of the certificate would remain after the change, Sgt Pepper could change ShowSessions to do whatever he fancies and see a lot of data from DMVs etc where he has no business. As it stands, if he changes the procedure, the signature disappears, and he needs to come back to you as the server DBA to have it signed again. And you will only sign it after having reviewed the code and made sure that it stays within what you permit.

The other observation is the fact that the powers of the certificate are not brought into user-written modules invoked by the signed procedure. When you are presented with a stored procedure to sign, you only have to look at the code for that procedure to determine whether it is OK. If the procedure invokes other stored procedures or can fire triggers through INSERT, UPDATE and DELETE, you don't need to investigate what those procedures or triggers do, because you know that they will not benefit from the certificate. And this is essential, since the power user can change these procedures or triggers at any time without affecting the signature.

However, this does not mean you don't have to be observant. In the appendix, there are several examples where we work with specific permissions that by their nature do affect inner scopes. For the particular permission we have worked with, VIEW SERVER STATE, there is a way that the local power users can lure you. Assume that Sgt Pepper presents you with a version of ShowSessions that includes a SELECT against an innocently looking table. Don't sign this! As soon as you leave the room, the Sergeant will drop the table and create a view that runs a query against his favourite DMV. Because the view is not a module of its own, the view will be evaluated in the presence of the certificate. And since the code of the stored procedure has not changed, the signature is still valid. Same goes for table-valued functions. It could be a multi-statement function and a module of its own when you are presented the code, but the power user can change it to a inline-table function later on.

Automating Signing for Server-Level Permissions

We have now seen the mechanisms, and we have seen that there are quite a few steps. Even more when we worked with database permissions, we yearn for a way to automate the process. For this purpose, I have composed a script which you find in the file GrantPermsToSP_server.sql. Why is this a script and not a stored procedure? My thinking is that if you are a DBA, you may need to run this script on ServerA on Monday, ServerB on Wednesday and so on. So it seems better to have a script that you have on your disk which you can improve by time, rather than having copies of a stored procedure on umpteen servers.

Note: This script requires SQL 2012 and higher. I also have a script for SQL 2005 and SQL 2008 that I will discuss at the end of this section.

As you might guess, this script is even longer than GrantPermsToSP, and here I only show the opening parameter part and discuss the output. The main body of the script is a good exercise of dynamic SQL, but that is not the topic for this article. The script itself is well commented.

The script starts up this way:

USE master
go
-- Set up parameters: the procedure to sign and the database it belongs to.
DECLARE @database nvarchar(260) = 'Playground',
        @procname nvarchar(520) = 'Management.ShowSessions'

-- The permissions to grant through the certificate. Leave table empty
-- to only remove current permissions.
DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('VIEW SERVER STATE')

-- Run with debug or not?
DECLARE @debug bit = 1

--============================ END OF SETUP ==========================

When you are to sign a procedure, you only need to edit the part before the line with the equal signs. You would only meddle in the part below this line, if there is something you want to improve in the process. The input is the same as for GrantPermsToSP, with the database as an additional input parameter. You specify the procedure name, and you can leave out dbo for procedures in this schema. You fill a table variable with the permissions you want to grant. You can also enter server roles and the script will figure that out and add the certificate login as a member in the role you specify. If you leave @perms empty, the script will only remove existing signatures, logins and certificates. Finally, there is a @debug flag which defaults to 1. You would have to be brave to set it to 0. :-)

Before we try to run the script, here is a summary of what the script does. These are the steps:

  1. Validate and normalise database and procedure names. This is to make sure that the script always generates the same name for the certificate, even if you use different case or you are inconsistent with specifying the schema.
  2. Generate the name, subject and password for the certificate.
  3. If a login with the certificate name exists, drop it.
  4. Drop any old certificate in master.
  5. If the procedure is signed with the old certificate, remove the signature.
  6. As a safety precaution, remove any user created from the certificate in the target database. (The script never creates such a user, but as a server DBA, you have no control over what the local power users are doing.)
  7. Drop the certificate in the target database, if it exists there.
  8. Create a new certificate in master.
  9. Create a login from the certificate.
  10. Grant permissions / role membership to the login.
  11. Retrieve the certificate with certencoded and certprivatekey.
  12. Import the certificate to the target database.
  13. Sign the procedure.
  14. Remove the private key from the certificate, both in the target database and in master.

The subject for the certificate is formed in the same way as in GrantPermsToSP, that is, it starts with GRANT and a summary of the permissions (or roles) with commas removed. Likewise, the password is a GUID. The name of the certificate and the login are formed in a different way, though. The name of both start with SIGN followed by a space and then the three-part name for the procedure, with all components in brackets. The name of the login and the certificate are the same; I have not bothered with a separate name for the login. Thus, the above results in a certificate that is called SIGN [Playground].[Management].[ShowSessions].

So why are the naming schemes different?

  1. It just happened that way. :-) (I devised the original script long before I wrote the procedure).
  2. If you have different naming schemes on server and database level, you avoid name clashes if a procedure needs to be granted both server-level and database-level permissions.
  3. Since you may have all sorts of certificates and logins on server level, it helps with names that directly convey what they are used for.

If you want a different naming scheme, feel free to change.

In the debug output, the statements are prepended with a comment which states in which database the command is executed.

Before you go on and test the script, alter the procedure to remove the existing signature and verify that Sgt Pepper now only can see his own session:

USE Playground
go
ALTER PROCEDURE Management.ShowSessions AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT

Now you can test running the full script GrantPermsToSP_server.sql. When you have done this, try running ShowSessions as Sgt Pepper once more to verify that the certificate login is added to the tokens and that Sgt Pepper can see all processes connected to Playground.

Here is the output I get when I run the script (abbreviating the blobs for the certificates and some lines broken to fit within the page width):

(1 row affected)

-- In database [Playground]
SELECT @procname = MIN(quotename(s.name) + '.' + quotename(o.name))
     FROM   sys.objects o
     JOIN   sys.schemas s ON o.schema_id = s.schema_id
     WHERE  o.object_id = object_id(@procname)

-- In master
DROP LOGIN [SIGN [Playground]].[Management]].[ShowSessions]]]

-- In master
DROP CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]

-- In database [Playground]
IF EXISTS (SELECT *
               FROM   sys.crypt_properties cp
               JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
               WHERE  cp.major_id = object_id(@procname)
                 AND  c.name = @certname)
        DROP SIGNATURE FROM [Management].[ShowSessions] 
           BY CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]

-- In database [Playground]
SELECT @username = NULL
    SELECT @username = dp.name
    FROM   sys.database_principals dp
    JOIN   sys.certificates c ON dp.sid = c.sid
    WHERE  c.name = @certname

-- In database [Playground]
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
       DROP CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]

-- In master
CREATE CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]
       ENCRYPTION BY PASSWORD = '51FF4DBD-A5BA-433A-9478-68AAA6A9FB57Aa0'
       WITH SUBJECT = 'GRANT VIEW SERVER STATE'

-- In master
CREATE LOGIN [SIGN [Playground]].[Management]].[ShowSessions]]] 
    FROM CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]

-- In master
GRANT VIEW SERVER STATE TO [SIGN [Playground]].[Management]].[ShowSessions]]]

-- In database [Playground]
CREATE CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]
       FROM BINARY = 0x308201C730820130A00302010202106C41...
       WITH PRIVATE KEY (BINARY = 0x1EF1B5B0000000000100000001000000...
            ENCRYPTION BY PASSWORD = '51FF4DBD-A5BA-433A-9478-68AAA6A9FB57Aa0',
            DECRYPTION BY PASSWORD = '51FF4DBD-A5BA-433A-9478-68AAA6A9FB57Aa0')

-- In database [Playground]
ADD SIGNATURE TO [Management].[ShowSessions] 
   BY CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]] 
   WITH PASSWORD = '51FF4DBD-A5BA-433A-9478-68AAA6A9FB57Aa0'

-- In master
ALTER CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]] 
    REMOVE PRIVATE KEY

-- In database [Playground]
ALTER CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]] 
    REMOVE PRIVATE KEY

Some notes:

The code above only executes on SQL 2012 and later. If you are unfortunate to be on SQL 2008 or SQL 2005, you can instead use GrantPermsToSP_server_2008.sql. This is the script that appeared in the original version of this article. It is somewhat less versatile, and I have not bothered to update it with new ideas. Notable differences:

One More Level: Availability Groups

If the database is part of an availability group, what we have done so far is not enough. The certificate must be copied to all servers in the availability group, and on each server a login must be created for the certificate and that login needs to be granted the permissions required. If we don't do this, the procedure will stop working after a failover.

Before you draw a deep sigh: don't worry, this is already handled in GrantPermsToSP_server.sql. If the database is in an availability group, the script loops over the servers in the AG and sets up a temporary linked server called TEMP$SERVER for each server and drops the linked server once that server has been processed. On each linked server, the script performs these actions:

  1. Drop any login with the generated name.
  2. Drop any existing certificate with the generated name.
  3. Imports the public key of the new certificate to the master database (but not the private key as it is not needed).
  4. Creates a new login from the certificate.
  5. Grants permissions to the login, including adding the login to server roles where requested.

Here is the output I got when running the script in an AG with three nodes. When I ran it, AGNODE1 was the current primary. I only show the output for the other two nodes (as the execution on AGNODE1 is the same as above). As previously, blobs are abbreviated, and some lines broken into several:

-- On server AGNODE2
IF EXISTS (SELECT *
                     FROM   sys.server_principals
                     WHERE  name = @certname
                       AND  type = 'C') 
             DROP LOGIN [SIGN [Playground]].[Management]].[ShowSessions]]]

-- On server AGNODE2
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
             DROP CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]


-- On server AGNODE2
CREATE CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]
                FROM BINARY = 0x308201C730820130A0030...

-- On server AGNODE2
CREATE LOGIN [SIGN [Playground]].[Management]].[ShowSessions]]] 
FROM CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]

-- On server AGNODE2
GRANT VIEW SERVER STATE TO [SIGN [Playground]].[Management]].[ShowSessions]]]

-- On server AGNODE3
IF EXISTS (SELECT *
                     FROM   sys.server_principals
                     WHERE  name = @certname
                       AND  type = 'C') 
             DROP LOGIN [SIGN [Playground]].[Management]].[ShowSessions]]]

-- On server AGNODE3
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
             DROP CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]


-- On server AGNODE3
CREATE CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]
                FROM BINARY = 0x308201C730820130A0030...

-- On server AGNODE3
CREATE LOGIN [SIGN [Playground]].[Management]].[ShowSessions]]] 
FROM CERTIFICATE [SIGN [Playground]].[Management]].[ShowSessions]]]

-- On server AGNODE3
GRANT VIEW SERVER STATE TO [SIGN [Playground]].[Management]].[ShowSessions]]]

Keep in mind that as always with linked servers, there can be hassle. Specifically, if you run GrantPermsToSP_server.sql from SSMS on your personal machine, the SQL Server you connect to may not be trusted for Kerberos delegation and you will face the dreaded double-hop error. It would probably be a better approach to handle the AG from a PowerShell script that connects to each server individually. But that is left as an exercise to the reader.

Interlude: Access to the Database Only through the Application

We have now learnt about using certificates to package database and server permissions in a stored procedure. Before we start looking at the other method, the EXECUTE AS clause, we will take a look at a problem that as such goes beyond the topic for this article. However, the learnings we will make in this chapter are important for the chapters that follow.

Ever so often I see questions in the SQL Server forums from people who want to prevent their users from accessing the database outside the application with SSMS, Excel etc. This may or may not be applications that use stored procedures. The hope is that there is a way to grant permissions to the application, but there is no secure way an application can identify itself to SQL Server. A rogue user can always build his own application that identifies itself as the real application. Instead you need to look for other solutions.

To achieve this in a secure way, there must be three tiers, one way or another. That is, you can never achieve this with a thick Windows client that sits on the users' desktop, because anything the application can do, the users can do on their own. There must be a middle tier between the user machines and SQL Server. This can be a full-blown application server, but if you have a web application, the web server serves as your middle tier in this context.

We will look at three methods:

  1. Application roles.
  2. Application login.
  3. Terminal server/Citrix.

The script for this chapter is 06_interlude.sql.

Application Roles

You create an application role with the statement CREATE APPLICATION ROLE and you need to define a password for the application role. Then you grant the role the permissions needed to run the application. The application activates the application role with sp_setapprole which requires the password for the role. When setting the role, the application retrieves a cookie from SQL Server, and it uses this cookie when reverting from the application role with sp_unsetapprole before disconnecting.

Reverting from the application role is not necessary for an application that sticks to a static connection, but most applications follow the pattern of connecting, running an SQL command or two before disconnecting within the same method. They rely on that the API maintains an connection pool from which connections are reused. However, SQL Server does not agree to the reuse of a connection on which an application role has been set, since the new logical connection has a different security context. Whence the need to unset the application role. The cookie is a security measure to prevent the user from being able to unset the application role, would there be an SQL injection hole in the application.

A full-fledged demo of application roles would require some client code, but we will let it suffice with a T‑SQL script. That is still sufficient to learn how application roles work:

SET XACT_ABORT, NOCOUNT ON
USE Playground
go
CREATE APPLICATION ROLE AppRole WITH PASSWORD = 'Tomorrow Never Knows'
GRANT EXECUTE, SELECT TO AppRole
go
EXECUTE AS LOGIN = 'SgtPepper'
CREATE TABLE #cookie(cookie varbinary(8000) NOT NULL)
DECLARE @cookie varbinary(8000)
EXEC sp_setapprole 'AppRole', N'Tomorrow Never Knows', 
                   @fCreateCookie = 'true', @cookie = @cookie OUTPUT
INSERT #cookie (cookie) VALUES (@cookie)
go
SELECT * FROM tokeninfo
SELECT * FROM logintokeninfo
EXEC Management.ShowSessions
INSERT Playtable(id, somedata) VALUES (-1, 'It was twenty years ago today')
go
DECLARE @cookie varbinary(8000)
SELECT @cookie = cookie FROM #cookie
EXEC sp_unsetapprole @cookie
DROP TABLE #cookie
go
REVERT

We first create the application role, which we grant EXECUTE and SELECT permission on database level (in difference to Playrole who only have EXECUTE permission on the dbo schema). We then become Sgt Pepper, remember that he is a db_owner user in Playground. We set the application role and save the cookie into a temp table, so that we don't lose it. (Would you lose the cookie, you will have no choice but to disconnect and reconnect, else you cannot get out of the application role.) In the next batch we inspect the contents of the tokeninfo and logintokeninfo views and attempt to run ShowSessions and insert a row into Playtable. Having done this, we retrieve the cookie and unset the application role and finally we revert from the impersonation of Sgt Pepper.

This is the output I get when running the above, with some repeating columns removed for brevity:

token_name type             usage         original_login    SYSTEM_USER DBUSER

---------- ---------------- ------------- ----------------- ----------- -------

AppRole    APPLICATION ROLE GRANT OR DENY LIVERPOOL\GMartin SgtPepper   AppRole

public     ROLE             GRANT OR DENY LIVERPOOL\GMartin SgtPepper   AppRole

 

token_name type             usage

---------- ---------------  -------------

public     SERVER ROLE      DENY ONLY

 

token_name                                    type                        usage

--------------------------------------------- --------------------------- ---------

public                                        SERVER ROLE                 DENY ONLY

SIGN [Playground].[Management].[ShowSessions] LOGIN MAPPED TO CERTIFICATE DENY ONLY

SIGN [Playground].[Management].[ShowSessions] CERTIFICATE                 DENY ONLY

 

Msg 50000, Level 16, State 1, Procedure ShowSessions, Line 8 [Batch Start Line 13]

You don't have permission to run this procedure!

Msg 229, Level 14, State 5, Line 17

The INSERT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.

Let's first look at the first result set, the contents from tokeninfo. There are two tokens: AppRole and public. But there is no trace of Sgt Pepper or db_owner which he is a member of. Furthermore, the function USER returns the name of the application role (the same is true for other functions that returns the current database user). That is, on database level, the application role has simply replaced Sgt Pepper as the current user. On the other hand, SYSTEM_USER still returns SgtPepper. The corollary of this is that if you have auditing or row-level auditing in your application based on USER or similar function, you will need to rework this, if you want to use application roles. On the other hand, if you are using a server-level function like SYSTEM_USER, suser_name() etc, you are good.

The next result set is from logintokeninfo and gives us the server-level tokens, and we can see that there is only one: public. SgtPepper is not present among the tokens, and nor is PowerUsers, the server role that Sgt Pepper is a member of. Furthermore, observe the column usage. So far, we have only seen GRANT OR DENY in this column, but now it reads DENY ONLY. This means that this token is not good for granted permissions, but only denied permissions. That is, if your application needs to do things on server level or in another database, you cannot use application roles. But there are plentiful of applications who are happy to work in a single database.

The third result set is again from logintokeninfo, but this time from inside of ShowSessions. The certificate and certificate login are now among the tokens, but they also have DENY ONLY. We see that ShowSessions fails. As it happens, the particular failure is due to that ShowSessions checks whether the user is a member of PowerUsers and that is not the case when the application role is in force. But would you rewrite the procedure to remove the check and re-sign it, the SELECT would still only return a row for the current session.

The final error message is from the attempt to insert a row into Playtable, which fails since AppRole does not have any INSERT permission. The fact that Sgt Pepper is db_owner is entirely irrelevant, since AppRole is the current user context.

Assuming that your application is not suffering from any of the limitations discussed above, should you use application roles? Maybe, but I cannot say that I am enthusiastic over them. I like to point out a few things:

Application Login

A common approach is that the application somehow authenticates the actual user, and logs on to SQL Server with a dedicated login. It goes without saying that the login must be performed from a middle tier, to keep the credentials of the application out of reach from the users.

There are quite a few ways, the actual user can be conveyed to SQL Server:

We will look at all of these.

Not at All

That is, the application never tells the database who is the actual user. This can be acceptable for some less important small-scale applications, but I would say that this space is growing smaller and smaller. Most systems require some form of auditing, either because of the business itself or because of outside regulations. And obviously, row-level security cannot be implemented in the database tier, if the database does not know the user.

In this setup, the application login needs to be granted all permissions needed, which preferably should not extend beyond db_datareader and db_datawriter to reduce the damage of SQL injection holes. Alas, it is not entirely uncommon to see sa used as the application login – this is an extremely bad idea!

Impersonating a Database User

In this case the application login needs only a single permission: IMPERSONATE on database level, since the first thing the application executes after connecting is:

EXECUTE AS USER = 'RealUser' WITH COOKIE = @cookie

The users may be SQL users who have been added to the database WITHOUT LOGIN or they can be Windows users, who only have been granted access to the database but who have no logins on server level. In either case, they cannot access SQL Server without the application.

We have already used the EXECUTE AS USER command quite a bit, but the clause WITH COOKIE is new. It serves the same purpose as the cookie with application roles. That is, once you have specified the clause WITH COOKIE, you must use that cookie when reverting to the original security context:

REVERT WITH COOKIE = @cookie

This prevents the user from exploiting any SQL injection hole to issue a REVERT command to perform actions as the application login.

As with application roles, applications that use connection pooling need to revert before disconnecting, or else the connection cannot be reused. Applications that keeps their connection open can specify the clause WITH NO REVERT with EXECUTE AS to prevent REVERT altogether.

We have already used EXECUTE AS USER to test actions inside the database. Let's now look at the effects on server level. Run this:

EXECUTE AS USER = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT

(Because the use of cookie or not does not affect the result, we can permit us to skip that part.) This is the output:

token_name type         usage       original_login     SYSTEM_USER DBUSER

---------- ------------ ----------- ------------------ ----------- ---------

SgtPepper  SQL LOGIN    DENY ONLY   LIVERPOOL\GMartin  SgtPepper   SgtPepper

public     SERVER ROLE  DENY ONLY   LIVERPOOL\GMartin  SgtPepper   SgtPepper

PowerUsers SERVER ROLE  DENY ONLY   LIVERPOOL\GMartin  SgtPepper   SgtPepper

 

token_name                                    type                        usage    

--------------------------------------------- --------------------------- ---------

SgtPepper                                     SQL LOGIN                   DENY ONLY

public                                        SERVER ROLE                 DENY ONLY

PowerUsers                                    SERVER ROLE                 DENY ONLY

SIGN [Playground].[Management].[ShowSessions] LOGIN MAPPED TO CERTIFICATE DENY ONLY

SIGN [Playground].[Management].[ShowSessions] CERTIFICATE                 DENY ONLY

 

Msg 50000, Level 16, State 1, Procedure ShowSessions, Line 8 [Batch Start Line 27]

You don't have permission to run this procedure!

In difference to when we used an application role, the tokens of SgtPepper and PowerUsers are not removed from the login tokens, but it does not help as the usage is DENY ONLY. That is, just like when we use an application role, we are not able to access things outside the database. Thus, this solution is only useful for applications that works entirely inside the database. (In case you wonder about the rationale for this behaviour, we will come back to this when we discuss TRUSTWORTHY later on.)

At first, it may seem that this solution requires a lot of administration for all these users WITHOUT LOGIN, but if the application server authenticates the users, it seems reasonable to delegate the duty of creating the database users to the application login, which would call a stored procedure which has been granted the permissions ALTER ANY USER with help of certificate signing.

One possible advantage with EXECUTE AS USER over application roles and session context is that you can control what the individual users can do in the database by granting them different permissions. Then again, I can't see myself building an application that relies on the permissions in SQL Server. How pretty is it if the user tries to save and gets a permission error because he/she only has SELECT permission? But it could be that you want to use the database permissions to protect yourself against glitches in the permission system in the application.

In difference to sp_setapprole, EXECUTE AS USER can appear in nested scopes (except when you use NO REVERT which must be executed on top level). Theoretically this permits you to embed EXECUTE AS USER and REVERT in the command batches to avoid extra network roundtrips. However, the batch may abort because of an error before REVERT is executed, and in this case you cannot revert in your client-side exception handler since the cookie was only in the SQL batch (unless you saved it into a temp table). One possible option is that the exception handler disconnects and immediately reconnects to swallow the error which the reconnection causes. While this may be doable, it gets a bit complex and you may prefer to run EXECUTE AS and REVERT in separate batches after all.

Note: There is an older (and deprecated) command SETUSER which has a similar effect. I have not investigated whether are any significant differences. In any case, you should stay away from SETUSER.

Impersonating a Database Login

This is very similar to the above, but the application uses EXECUTE AS LOGIN for impersonation instead and thus needs IMPERSONATE permission on server level. With this arrangement, there are no restrictions for actions that require server-level permissions. But instead you need different measures to prevent the users from logging into SQL Server from outside the application. There are a couple of possible solutions:

It's not a bad idea to combine these, to avoid that a single accidental change suddenly permits users to connect.

Using Session Context (Context Info)

In this solution, the application uses the session context to set information about the current user. The session context is a user-defined area that programmers can use to set session-global values. Up to SQL 2014 it was known as "context info", and all there was to play with was 128 bytes. In SQL 2016 the concept was vastly improved, and we can now use up to 256 KB for session context. When using session context, all access to tables, stored procedures etc are by the application login; that is, there is no impersonation. As with application roles, the application login should at most be granted membership in db_datareader and db_datawriter. If the application login needs to perform privileged actions beyond this, put these actions in stored procedures you have signed with a certificate.

There are two ways to use the session context. Let's first look at sp_set_session_context that was introduced in SQL 2016. This procedure accepts key-value pairs that you can set. Optionally, you can define a key as read-only, which is very good when you use it to define a user name to be used for auditing, as this prevents a malicious user to use an SQL-injection hole to change his name. Here is one example (but don't run this yet):

EXEC sp_set_session_context 'Username', 'PolythenePam', @read_only = 1

To access data from the session context, you use the session_context function. When you use it for auditing, row-level security etc, you need to account for access outside the application by the DBA and other licensed persons where session_context may return NULL. So you need to do as in this example:

CREATE TABLE audited
   (somedata int NOT NULL,
    moduser  sysname NOT NULL
       CONSTRAINT def_moduser DEFAULT
          coalesce(convert(nvarchar(128), session_context(N'Username')),
                   original_login())
)

session_context returns sql_variant, which is why you need the conversion. Since you have to repeat this expression in many places, you may prefer to put it in a user-defined function, although this can incur some overhead for mass-updates. Here is a demo you can try:

INSERT audited(somedata) VALUES (9)
EXEC sp_set_session_context 'Username', 'PolythenePam', @read_only = 1
INSERT audited(somedata) VALUES (64)
SELECT somedata, moduser FROM audited

This is the output for GMartin:

somedata    moduser

----------- ------------------

9           LIVERPOOL\GMartin

64          PolythenePam

In difference to the other methods, there are no conflicts with connection pooling – the session context is automatically cleared when a connection is reused. Another nice thing is that you don't need any user administration whatsoever on the server, and the users can be entirely locked out.

All and all, this makes session context quite a palatable solution. It does require that you use the session_context function (or the UDF you have packaged it in) consistently in your database. Then again, the same applies for whatever auditing scheme you use – there are many functions to use, but you should the same throughout your system.

A possible drawback for the DBA who is monitoring the system is that the session context cannot be collected in Trace or Extended Events, so it is not possible to find the actual user, would that be needed.

In SQL 2014 and earlier, sp_set_session_context and session_context are not available, but instead you can use the command SET CONTEXT_INFO to set the user name. This command accepts a value of the type varbinary(128). Here is an example:

DECLARE @contextinfo varbinary(128)
SELECT @contextinfo = convert(varbinary(128), N'RockyRacoon')
SET CONTEXT_INFO @contextinfo

To use it, you use the function context_info as in this example:

CREATE TABLE also_audited
   (somedata int NOT NULL,
    moduser  sysname NOT NULL
       CONSTRAINT also_def_moduser DEFAULT
          coalesce(convert(nvarchar(64),
                      substring(context_info(), 1, 
                                charindex(0x0000, context_info()) - 1)),
                   original_login())
)

This certainly even more calls for being packaged in a UDF!

Here is a script for testing, which also clears context info, in case you already ran the command above.

SET CONTEXT_INFO 0x
INSERT also_audited(somedata) VALUES (9)
DECLARE @contextinfo varbinary(128)
SELECT @contextinfo = convert(varbinary(128), N'RockyRacoon')
SET CONTEXT_INFO @contextinfo
INSERT also_audited(somedata) VALUES (64)
SELECT somedata, moduser FROM also_audited

A drawback with SET CONTEXT_INFO is that it does not provide a way to make the value read-only, so an SQL injection hole could permit a malicious user to hide his identity and play someone else. Which, if there is row-level security involved, can be a quite serious breach!

(On the other hand, the monitoring DBA may prefer context info over the new session context, since the value of context_info can be collected in Extended Events.)

It is worth noting that SET CONTEXT_INFO is different from all other SET commands: normally when you issue a SET command inside a stored procedure, the effect of the SET command is reverted when the procedure exits, but this is not true for SET CONTEXT_INFO. Just like session context, the context info is cleared when a connection is reused in the connection pool.

Terminal Server/Citrix

So when you find that you have painted yourself into a corner and realised your fat client cannot be transformed to a three-tier application within reasonable cost, and you still want to keep users away from SQL Server outside the application, there is still one way out. You can put the application on Terminal Server or a solution like Citrix. That is, to run the application, the users log on to another server, the login script starts the application, and if the application stops, they are logged out.

To prevent the users from accessing SQL Server, you segment the network, so they cannot access SQL Server from their desktops and laptops.

It is not a bad idea to combine this with application roles and grant no permissions to the users. This sets up an extra security layer, in the case there is an error with the network configuration that opens access to SQL Server to the users.

The technical details how to implement this are entirely beyond of the scope for this article.

Using EXECUTE AS on Database Level to Package Permissions

We will now go back to the main theme of this article, that is, how to package permissions in a stored procedure when our needs go beyond what is possible with ownership chaining. This time, we will now look at the EXECUTE AS clause. If you found certificate signing to be a bit complicated, you will be delighted to find that this method is easier to use. In fact, a little too easy, as you will learn.

The script for this chapter is 07_executeasdb.sql.

The Recipe

Earlier we worked with check_playdata, which checks whether Playtable exists before running a query against it. Here is a version with a different name (so we can keep the signed version) that includes the diagnostic SELECT from tokeninfo.

SET XACT_ABORT, NOCOUNT ON
USE Playground
go
CREATE PROCEDURE check_playdata2 @id int AS
   SELECT * FROM tokeninfo
   IF object_id('Playtable') IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'

As a recap, we run this as Michelle:

EXECUTE AS USER = 'Michelle'
EXEC add_playdata 11, 'Strawberry Fields Forever'
EXEC add_playdata 12, 'Penny Lane'
EXEC check_playdata2 11
go
REVERT

We see something like this:

id    somedata                   whodidit

----- -------------------------- -----------

11    Strawberry Fields Forever  Michelle

 

id    somedata                   whodidit

----- -------------------------- -----------

12    Penny Lane                 Michelle

 

token_name type     usage         original_login    SYSTEM_USER      DBUSER

---------- -------- ------------- ----------------- ---------------- --------

Michelle   SQL USER GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-24966... Michelle

public     ROLE     GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-24966... Michelle

Playrole   ROLE     GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-24966... Michelle

 

Playtable has not been created yet. Try again later.

Ownership chaining applies in add_playdata which is just doing INSERT and SELECT on Playtable. But since ownership chaining does not apply to metadata access, and none of the tokens present carry any permission on Playtable, Michelle is incorrectly told that the table is unavailable when she runs check_playdata2. Let's address this by using the EXECUTE AS clause. Here is the recipe:

  1. Create a user WITHOUT LOGIN with the name derived from the stored procedure.
  2. Grant that user the required permissions (which could be role membership).
  3. Add an EXECUTE AS clause to the procedure.

When it comes to the first step, we have already used CREATE USER WITHOUT LOGIN. When we create a user from a certificate, this is a special user that cannot be impersonated or used for anything else than bridging certificate and permission. With EXECUTE AS there is nothing such special, but we use a regular database user, albeit one without a login.

Here are all three steps in the recipe at once, with the EXECUTE AS clause highlighted:

CREATE USER check_playdata2$user WITHOUT LOGIN
GRANT VIEW DEFINITION ON Playtable TO check_playdata2$user
go
ALTER PROCEDURE check_playdata2 @id int 
   WITH EXECUTE AS 'check_playdata2$user' AS
   SELECT * FROM tokeninfo
   IF object_id('Playtable') IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE 
      PRINT 'Playtable has not been created yet. Try again later.'

Michelle gives it a go:

EXECUTE AS USER = 'Michelle'
EXEC check_playdata2 11
go
REVERT

The output this time is quite different (I have split up the first result set on several lines to fit the page):

token_name                     type       usage

------------------------------ ---------- ---------------

check_playdata2$user           SQL USER   GRANT OR DENY

public                         ROLE       GRANT OR DENY

 

    original_login     SYSTEM_USER       DBUSER

    ------------------ ----------------- -----------------------------

    LIVERPOOL\GMartin  S-1-9-3-148309... check_playdata2$user

    LIVERPOOL\GMartin  S-1-9-3-148309... check_playdata2$user

 

id    somedata                    whodidit

----- --------------------------- ----------

11    Strawberry Fields Forever   Michelle

Michelle can now see the data in Playtable, but there is something else in this picture that is more interesting. Look at the information in tokeninfo: there is no trace of Michelle, but she has been replaced by check_playdata2$user. Because both Michelle and check_playdata2$user have been created WITHOUT LOGIN, the output from SYSTEM_USER does not stand out, but if you compare the output with the above, you can see that the SID is not the same. That is, Michelle has been entirely eradicated. The only other information present is GMartin who originally logged into SQL Server.

Thus, this is a quite a different mechanism from certificate signing. It's not a matter of adding a security token, but it is impersonation, exactly what we have already used the EXECUTE AS statement for. This has some quite some ramifications which we will analyse later in this chapter.

Using EXECUTE AS OWNER

I hear you: Yeah, this was simpler than certificate signing. But there is still some hassle with this proxy user and figuring out the right permissions, isn't there something simpler?

Indeed there is. For the lazy and casual, there is a very quick solution as demonstrated by a version of dynamic_playdata:

CREATE PROCEDURE dynamic_playdata2 @searchstr nvarchar(40) 
   WITH EXECUTE AS OWNER AS
   DECLARE @sql nvarchar(MAX)
   SELECT * FROM tokeninfo
   SELECT @sql = 'SELECT * FROM dbo.Playtable 
                  WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
   EXEC(@sql)
go
EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata2 'berry'
go
REVERT

The output when Michelle runs this:

token_name    type       usage          original_login     SYSTEM_USER  DBUSER

------------- ---------- -------------- ------------------ ------------ --------

dbo           SQL USER   GRANT OR DENY  LIVERPOOL\GMartin  sa           dbo

public        ROLE       GRANT OR DENY  LIVERPOOL\GMartin  sa           dbo

db_owner      ROLE       GRANT OR DENY  LIVERPOOL\GMartin  sa           dbo

 

id          somedata                                 whodidit

----------- ---------------------------------------- ----------

11          Strawberry Fields Forever                Michelle

Look! Four extra words was all it took. AS OWNER here means the owner of the procedure, which in this case (as often) is dbo, and you can tell from the output from tokeninfo that indeed it was dbo that was impersonated.

While simple, it breaks the principle of not granting more permissions than needed. And Michelle will be quick to show you why. Look what she is up to:

EXECUTE AS USER = 'Michelle'
go
CREATE PROCEDURE #xploit AS ALTER ROLE db_owner ADD MEMBER Michelle
go
EXEC dynamic_playdata2 'zz''EXEC #xploit--'
DROP PROCEDURE #xploit
SELECT is_member('db_owner') AS is_dbowner
go
REVERT

She first creates a temporary stored procedure (all users have the permission to do this) of which the body makes her member of db_owner. Then she runs dynamic_playdata2 which is a poorly written procedure with a hole open for SQL injection. She first enters zz to prevent the procedure from returning too much data. She then enters a single quote to close the search string. Now she adds a call to her procedure and finally she adds comment characters to kill the rest of the original command. This is the actual command that is executed:

SELECT * FROM dbo.Playtable 
                  WHERE somedata LIKE '%' + 'zz' EXEC #xploit--' + '%'

And as you can see of the result from is_member, the exploit worked. Michelle is now member of db_owner.

You may ask what the point is with the temporary stored procedure. Why didn't she inject the ALTER ROLE command directly into the SQL string? The answer is simply that the parameter length of 40 is a just a little short for this to fit. But Michelle worked around this with help of the temporary stored procedure. It is true, though, that this requires that she has direct access to run queries in SSMS or similar. If she had been using an application with any of the solutions in the previous chapter, this exploit would not have been possible. Then again, you may recall that in the original dynamic_playdata, the sloppy programmer hade made the parameter nvarchar(MAX), and in that case there is no need for temporary stored procedures, but everything fits.

Of course, you could argue that as long as the dynamic SQL is correctly constructed and is parameterised, there are no injection holes, and there should be no problems with using EXECUTE AS OWNER. But keep in mind that it only takes one bad programmer to take down that line of defence. So there is all reason to have a second one by granting as few permission as needed, be that through a proxy user or a certificate signature.

Before we move on, take Michelle out of db_owner:

ALTER ROLE db_owner DROP MEMBER Michelle

The Ramifications of Impersonation

We have seen that the effects of impersonation extend into dynamic SQL, as was the case with the token of the certificate user. How does it work in other contexts? We recreate the trigger we had earlier and create a copy of outer_sp. For the sake of the demo, we also create a user for outer_sp2, although this procedure does not need any extra permissions.

CREATE TRIGGER play_tri ON Playtable AFTER INSERT AS
   SELECT 'In trigger' AS wherearewe, *
   FROM   tokeninfo
go
CREATE USER outer_sp2$user WITHOUT LOGIN
go
CREATE PROCEDURE outer_sp2 @id int, @somedata nvarchar(40)
   WITH EXECUTE AS 'outer_sp2$user' AS
   SELECT 'outer_sp' AS wherearewe, *
   FROM   tokeninfo
   INSERT Playtable(id, somedata)
      VALUES(@id, @somedata)
   EXEC inner_sp

We run outer_sp2 as Michelle:

EXECUTE AS USER = 'Michelle'
EXEC outer_sp2 13, 'Yesterday'
go
REVERT

The output is something like this (with the result sets split up over two lines):

wherearewe token_name      type     usage

---------- --------------- -------- --------------

outer_sp   outer_sp2$user  SQL USER GRANT OR DENY

outer_sp   public          ROLE     GRANT OR DENY

 

   original_login    SYSTEM_USER       DBUSER

   ----------------- ----------------- --------------

   LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user

   LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user

 

wherearewe token_name      type     usage

---------- --------------- -------- --------------

In trigger outer_sp2$user  SQL USER GRANT OR DENY

In trigger public          ROLE     GRANT OR DENY

 

   original_login    SYSTEM_USER       DBUSER

   ----------------- ----------------- --------------

   LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user

   LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user

 

wherearewe token_name      type     usage

---------- --------------- -------- --------------

inner_sp   outer_sp2$user  SQL USER GRANT OR DENY

inner_sp   public          ROLE     GRANT OR DENY

 

   original_login    SYSTEM_USER       DBUSER

   ----------------- ----------------- --------------

   LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user

   LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user

You may recall that when we did the same experiment with certificates, the token of the certificate user was removed when we entered another user-written module. But something like that does not happen here. The effect of the impersonation is in effect until outer_sp2 exits. (Or there is some inner module that has its own EXECUTE AS clause.)

Let's also look in Playtable:

SELECT * FROM Playtable

This is what we find:

id          somedata                    whodidit

----------- --------------------------- --------------

11          Strawberry Fields Forever   Michelle

12          Penny Lane                  Michelle

13          Yesterday                   outer_sp2$user

That is, the row inserted into Playtable in outer_sp is said to have been inserted by outer_sp2$user and not by Michelle. But outer_sp2$user is supposed to only be a proxy for packaging permissions. The real user is Michelle.

Once impersonation is in play, all those functions that return the current server login or current database user: SYSTEM_USER, suser_name(), suser_sname(), suser_id(), suser_sid(), CURRENT_USER, USER, SESSION_USER, user_id() and user_name() returns information about the impersonated user. And it does not matter whether impersonation was initiated with the statement EXECUTE AS or by the clause EXECUTE AS in a stored procedure or other module, they are just two faces of the same thing.

These functions are frequently used in SQL Server applications. One use for them is to answer questions in the application like "is this user entitled to see or modify this data"? The typical example is row-level security (RLS). Say that you put an EXECUTE AS clause with a proxy user in a stored procedure to package permissions, and this procedure includes access against a table or view with row-level security, be that a home-brew or something that uses the new built-in RLS feature added in SQL 2016. All of a sudden no rows come back. Or even worse: you use EXECUTE AS OWNER, and the rules are set up to let dbo see everything.

The most common usage for these system functions might be auditing. They could appear in defaults as in our Playtable, or they could be used in triggers that writes to log tables. And as we saw above, this auditing will be incorrect when we use the EXECUTE AS clause.

Auditing may also be performed through SQL Trace (which is what Profiler uses) based on the columns LoginName or NTUserName or through Extended Events sessions that use nt_username, server_principal_name or username. All of which change values when impersonation is in effect. You need to ask yourself: when it is of interest to audit the proxy user in the EXECUTE AS clause? Probably never.

If you use the SQL Audit feature, the audit file will always include the name of the user who actually logged as well as the name of the impersonated user. Beware, though, that when you set up a database audit with CREATE DATABASE AUDIT SPECIFICATION and filter by a database principal, the filter will work on the impersonated principal and not the original login, which means that you may miss actions that should have been audited – or that you get too much in your audit log, because you filtered by dbo and then used EXECUTE AS OWNER all over the place.

This does not mean that you cannot use EXECUTE AS if you want row-level security or auditing, but you need to plan ahead and design your system accordingly. That is, you cannot use any of the functions listed above, but you must use one of original_login() or session context/context info.

original_login() returns the user who actually logged into SQL Server and as we have seen in the output from the tokeninfo view, it is unaffected by impersonation. In Trace, the corresponding column to collect is SessionLoginName and with Extended Events it is session_server_principal_name.

In the previous chapter we looked at how an application login could use any of the EXECUTE AS statements to impersonate the actual user. In this architecture, original_login() does not work, because it returns the name of the application login. Nor is there any function to get the names in the middle of the impersonation stack. Thus, if you have this setup, and you want to use EXECUTE AS in your stored procedures, your only option to pass the name of the actual user to auditing and RLS filters is session context / context info which we also looked at in the previous chapter.

By now you may have to come to the realisation that what initially seemed to be a simple solution, in fact is not really that simple. While using certificates at first seemed like complicated mumbo-jumbo, it only affects the actual procedure you sign, and you can easily automate the process in a stored procedure or in a script. EXECUTE AS on the other hand requires that you consider the entire architecture of your application before you can start using it. If you are already using original_login() or session context, you can go ahead, but else you have work to do.

If you know that your system uses SYSTEM_USER all over the place, you may be a little scared. What if your developers learn about EXECUTE AS OWNER from somewhere without understanding the consequences? Maybe they have already done so? Here is a query that lists all procedures in the database with an EXECUTE AS clause:

SELECT s.name + '.' + o.name AS Module,
       CASE sm.execute_as_principal_id WHEN -2 THEN 'OWNER'
            ELSE user_name(sm.execute_as_principal_id)
       END AS [EXECUTE AS]
FROM   sys.sql_modules sm
JOIN   sys.objects o ON sm.object_id = o.object_id
JOIN   sys.schemas s ON s.schema_id = o.schema_id
WHERE  sm.execute_as_principal_id IS NOT NULL

This is the output in the Playground database:

Module                    EXECUTE AS

------------------------- -----------------------

dbo.dynamic_playdata2     OWNER

dbo.check_playdata2       check_playdata2$user

dbo.outer_sp2             outer_sp2$user

If you don't want this ever to happen, and you are the evil sort of person, you may want to add a DDL trigger to your database to stop this from happening. You can find such a trigger and a test procedure in the script StopExecAs.sql. If you play with it in Playground, be sure to drop the trigger once you have completed your testing.

Before I close this section, I should add that even if your database relies on SYSTEM_USER or any similar function, it may still be alright to use EXECUTE AS for a specific procedure, because you have concluded that this particular procedure does not affect any area where this matters. For instance, say that in your application you want to permit super users to add users to the database. If you create a procedure with EXECUTE AS that do nothing more than CREATE USER, that should be alright. There could be an outer procedure which handles the auditing required for the operation. Again, this is something that requires understanding of what you are doing, and personally, I much prefer certificate signing over EXECUTE AS.

Various Titbits

In this section I will cover various titbits around the EXECUTE AS clause that I have not mentioned so far.

EXECUTE AS SELF

Another variant of the EXECUTE AS clause is to say WITH EXECUTE AS SELF. This is short for saying WITH EXECUTE AS 'yourusername'. I will have to admit that I have not really seen the use case for this.

EXECUTE AS CALLER

You can also say:

CREATE PROCEDURE myproc WITH EXECUTE AS CALLER AS

This is merely stating the default. That is, this is no different from saying

CREATE PROCEDURE myproc AS

You can also use EXECUTE AS CALLER as a statement inside a stored procedure. This permits you to revert to the caller's context so you can retrieve information about the caller. For instance, you could do:

EXECUTE AS CALLER
SELECT @actual_user = SYSTEM_USER
REVERT

Thus, this is something that can help you to overcome the effects of impersonation, but only in the procedure that has the EXECUTE AS clause. If a trigger invoked by the procedure would get the idea to try EXECUTE AS CALLER, it would only get the user name of the proxy user, that is, the direct caller of the trigger.

You could also use EXECUTE AS CALLER for the main bulk of the procedure, and revert back to the impersonated user for the action that requires special powers. For instance:

CREATE PROCEDURE check_playdata3 @id int 
   WITH EXECUTE AS 'check_playdata2$user' AS
   DECLARE @object_id int = object_id('Playtable')
   EXECUTE AS CALLER
      IF @object_id IS NOT NULL
         SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
      ELSE
         PRINT 'Playtable has not been created yet. Try again later.'
   REVERT
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata3 12
go
REVERT

This runs fine and returns the correct information in the whodidit column, but at the price of the code being more cluttered.

EXECUTE AS USER in the procedure body?

The above may inspire you to think: why not simply use EXECUTE AS USER in the procedure around the statement where extra powers are needed? That would be more logical and emphasise the special part. For instance like this:

CREATE PROCEDURE check_playdata4 @id int AS
   EXECUTE AS USER = 'check_playdata2$user'
     DECLARE @object_id int = object_id('Playtable')
   REVERT
   IF @object_id IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'

Michelle tries this procedure:

EXECUTE AS USER = 'Michelle'
EXEC check_playdata4 12
go
REVERT

But there is no success:

Msg 15517, Level 16, State 1, Procedure check_playdata3, Line 2

Cannot execute as the database principal because the principal "check_playdata2$user" does not exist, this type of principal cannot be impersonated, or you do not have permission.

When EXECUTE AS appears as a statement in a procedure, this is the same as with all other "advanced" statements: the user must have direct permission to impersonate the user. It is only when it appears as a clause that this requirement is voided.

Overcoming DENY

You may recall that when we issued an explicit DENY for Michelle, the token from the certificate user did not help to overcome that. This is different with EXECUTE AS, since this is impersonation and the context of the original user is lost. So even if you prefer certificate signing in general, you may turn to EXECUTE AS if you end up in a situation where a DENY is blocking the way. (But as I noted previously, you should be very restrictive with using DENY and normally it should not be needed.)

Natively Compiled Stored Procedures in SQL 2014

If you are using natively compiled procedures in SQL 2014, the EXECUTE AS clause is mandatory, and you cannot use EXECUTE AS CALLER. The effects of this are, however, quite limited, since in SQL 2014 none of the functions to return the information about the current user are available in natively compiled stored procedures.

This restriction does not apply for SQL 2016, but here you can leave out EXECUTE AS or use EXECUTE AS CALLER. And functions like original_login(), SYSTEM_USER and USER are available.

EXECUTE AS and Server-Level Permissions

We have now looked at using EXECUTE AS to package permissions on database level. You may have noticed that I am not entirely enthusiastic over EXECUTE AS and that I favour certificate signing. But you are excited over the simplicity that EXECUTE AS offers, so you want to use it on server level as well. You say to yourself: how difficult can it be? Just create a proxy login that I disable so that it cannot be used, grant that login the permissions needed and add EXECUTE AS. Well, maybe. We'll see.

The script for this chapter is 08_execasserver.sql.

Into the Sandbox

Let's try the idea with the procedure ShowSessions. As you may recall the problem was that we wanted power users to be able to see all sessions connected to their database, but they should not see other users. This is how the procedure looked like last time we looked at it:

CREATE PROCEDURE Management.ShowSessions AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1 
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)

We will make a ShowSessions2 where we use EXECUTE AS rather than certificate signing. First we need that proxy login, so let's create it:

SET XACT_ABORT, NOCOUNT ON
USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN [PROXY Playground.Management.ShowSessions2] ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
go
ALTER LOGIN [PROXY Playground.Management.ShowSessions2] DISABLE
DENY CONNECT SQL TO [PROXY Playground.Management.ShowSessions2]
GRANT VIEW SERVER STATE TO [PROXY Playground.Management.ShowSessions2]

We use the same naming pattern as we used for certificates for server-level signing. That is, we have a prefix which directly informs us about the purpose, and then there is the full three-part name of the procedure. We create a random password for this login that we never display, and we disable the login and we also explicitly DENY the login the permission CONNECT SQL – this is the permission you need to be able to log on to SQL Server. Since this login is just a proxy, it should never log in on its own.

So now we can copy the code above and add the EXECUTE AS clause? What could go wrong?

USE Playground
go
CREATE PROCEDURE Management.ShowSessions2
   WITH EXECUTE AS 'PROXY Playground.Management.ShowSessions2' AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions2
go
REVERT

This is the error message:

Msg 916, Level 14, State 1, Procedure ShowSessions2, Line 0

The server principal "PROXY Playground.Management.ShowSessions2" is not able to access the database "Playground" under the current security context.

Oops, we must also add the proxy login as a user in the Playground database.

CREATE USER [PROXY Playground.Management.ShowSessions2]

Which fails with:

Msg 15023, Level 16, State 1, Line 31

User, group, or role 'PROXY Playground.Management.ShowSessions2' already exists in the current database.

This may seem surprising, but if you look closely above, you realise that the error message about the proxy login not being able to access the database came when Sgt Pepper tried to execute the procedure, not when the procedure was created. (Since else there would have been a message about the procedure missing.) When we created the procedure, SQL Server was helpful and created the user in the database for us, as this is needed for the proxy user to be recorded in sys.sql_modules as execute_as_principal. However, the user was not granted CONNECT permission and whence the error. Grant it, and try it again as Sgt Pepper.

GRANT CONNECT TO [PROXY Playground.Management.ShowSessions2]
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions2
go
REVERT

But there is a new error message:

Msg 50000, Level 16, State 1, Procedure ShowSessions2, Line 9

You don't have permission to run this procedure!

If you have been paying attention, you already understand why we are getting this error. In the procedure we are impersonating the proxy login, but the proxy is not a member of PowerUsers, nor does it have the CONTROL SERVER permission. In fact, the entire idea with this check is that the DBA only wants the trusted PowerUsers to run this procedure, even if it resides in a user database.

This is a situation when we need to use EXECUTE AS CALLER to get back to get information about the actual user. Here is a modified version of the procedure:

ALTER PROCEDURE Management.ShowSessions2
   WITH EXECUTE AS 'PROXY Playground.Management.ShowSessions2' AS
   SELECT * FROM logintokeninfo

   DECLARE @has_perm bit = 0
   EXECUTE AS CALLER
      IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
          has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
          SELECT @has_perm = 1
   REVERT

   IF @has_perm = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)

We wanted to use EXECUTE AS, because it seemed simpler than certificates, but we had to start with first making the procedure more complex. But that's only the beginning. Before when you make the next attempt, make sure that you have multiple connections open to the Playground database:

EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions2
go
REVERT

No error message this time, but Sgt Pepper only sees one session. What if we try this ourselves? We are after all sysadmin, who could stop us?

EXEC Management.ShowSessions2

But the output is the same (for brevity I have abbreviated the name of the proxy login and reduced the result set from sys.dm_exec_sessions:

token_name   type        usage       original_login     SYSTEM_USER  DBUSER

------------ ----------- ----------- ------------------ ------------ --------

PROXY ...    SQL LOGIN   DENY ONLY   LIVERPOOL\GMartin  PROXY ...    PROXY ...

public       SERVER ROLE DENY ONLY   LIVERPOOL\GMartin  PROXY ...    PROXY ...

 

session_id login_time              host_name

---------- ----------------------- --------------

54         2017-08-13 19:57:20.337 LIVERPOOL

The answer to the puzzle is in the usage column: it says DENY ONLY. That is, any granted permissions on server level do not apply, only explicit DENY. In the interlude chapter we looked at using an application login which impersonates the actual user with EXECUTE AS USER. In this case, we observed that the impersonation was only good in the current database, but not outside of it. And this is the same thing. As you may understand from the exercise with adding the proxy login as a user to the Playground database, the EXECUTE AS clause means that it is a database user that is being impersonated and not a server login.

When you impersonate a database user, you are sandboxed into the current database, you cannot access things outside the database, be that things on server level or in another database, even if the user you impersonate maps to a login with powers in the place you want to go. As you will learn in the next section, there is a good reason for this.

The Dangers of TRUSTWORTHY

The sandbox is not hermitically sealed. You can perform actions outside of it, but two doors need to be opened. One door leading out of the sandbox, and one door leading into the place where you want to go.

As you may recall, the Playground database is owned by sa, so the second door is already open. All you need to do is this:

ALTER DATABASE Playground SET TRUSTWORTHY ON

Note: To change the TRUSTWORTHY setting of a database, you must have the permission CONTROL SERVER. Beware that if you restore a copy of the database on a different server or on the same server with a different name, the restored database will not be marked as trustworthy.

Sgt Pepper tries again:

EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions2
go
REVERT

Success! All connections to Playground are displayed (I'm leaving out some columns for brevity):

token_name                                 type         usage

------------------------------------------ ------------ --------------

PROXY Playground.Management.ShowSessions2  SQL LOGIN    GRANT OR DENY

public                                     SERVER ROLE  GRANT OR DENY

sa                                         SQL LOGIN    AUTHENTICATOR

 

session_id login_time              host_name

---------- ----------------------- -----------

51         2017-08-12 17:49:39.307 LIVERPOOL

53         2017-08-13 16:26:45.833 LIVERPOOL

54         2017-08-13 19:57:20.337 LIVERPOOL

If we look at the contents from the logintokeninfo view, we can see that the usage column now reads GRANT OR DENY. Furthermore, there is a third row with sa as the token name and with the usage AUTHENTICATOR. This type of token is added when a user is impersonated on database level and the database is set as TRUSTWORTHY. The purpose of the AUTHENTICATOR token is to authenticate the impersonated user on server level, and for this to happen, the token must carry the permission AUTHENTICATE SERVER. (Or if the access is to another database, AUTHENTICATE permission in that database.)

That may sound a little abstract, and it is not really important to understand the exact mechanism. A simplified description could be: if the database owner has the appropriate permissions, it vouches for the other tokens on server level, and that is why the login tokens now have GRANT OR DENY and thus their permissions are active on server level.

Sgt Pepper was able to run ShowSessions with EXECUTE AS + TRUSTWORTHY. Does this mean that everything is OK then? We have achieved a solution which is much simpler than certificate signing? Simpler maybe – but definitely not OK.

First of all, think in terms of separation of duties. We wanted Sgt Pepper to be able to see all connections to the Playground database, but he must not see connections to other databases, as that could disclose login names he should not see. We achieved this with certificates, because Sgt Pepper cannot change the procedure with less than the signature disappearing. So after every change, he has to come to you as the DBA to have the procedure signed again, and you would only agree to do this as long as you can ascertain that the Sergeant stays within his database. With the solution we have now, Sgt Pepper has gotten carte blanche to change the procedure as he sees fit. For instance, he can remove the filter on database_id to see all connections in the server. Exactly what we wanted to avoid.

But that is not all. For what follows, I will not use EXECUTE AS LOGIN to play Sgt Pepper, but to make it perfectly clear of what is going on and to remove any doubts, you should login directly as Sgt Pepper (the password is Lonely Hearts Club Band). Do this by right-clicking in the query window in Management Studio and select Change Connection to switch forth and back between Sgt Pepper and yourself. This is also called out in the accompanying script file. Connected as Sgt Pepper run:

USE Playground
go
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo

The output is:

Sysadmin token_name type        usage         original_login SYSTEM_USER DBUSER

-------- ---------- ----------- ------------- -------------- ----------- ---------

0        SgtPepper  SQL LOGIN   GRANT OR DENY SgtPepper      SgtPepper   SgtPepper

0        public     SERVER ROLE GRANT OR DENY SgtPepper      SgtPepper   SgtPepper

0        PowerUsers SERVER ROLE GRANT OR DENY SgtPepper      SgtPepper   SgtPepper

There is nothing remarkable about this. Sgt Pepper is logged in as himself, and he is a member of public and PowerUsers. He is not member of sysadmin. But look what happens now:

EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
--DBCC TRACEON(4199)
REVERT

Because Sgt Pepper is db_owner, he has the permission to do anything inside the database. For instance, if he feels like he can impersonate dbo. Look at the output:

Sysadmin token_name type        usage         original_login SYSTEM_USER DBUSER

-------- ---------- ----------- ------------- -------------- ----------- ------

1        sa         SQL LOGIN   GRANT OR DENY SgtPepper      sa          dbo

1        public     SERVER ROLE GRANT OR DENY SgtPepper      sa          dbo

1        sysadmin   SERVER ROLE GRANT OR DENY SgtPepper      sa          dbo

1        sa         SQL LOGIN   AUTHENTICATOR SgtPepper      sa          dbo

Sgt Pepper is now sysadmin and is taking over the server! If you think this is fake, you can try running the DBCC command which requires membership in sysadmin and it runs without error. (This particular trace flag enables optimizer fixes, and it is actually often a good idea to have it on).

This is a classical case of privilege elevation, and it happened because sa owns the database, and we just too casually set the database TRUSTWORTHY. As I said, initially, it is not good practice to have sa as the database owner. Yet, this is by no means uncommon. One reason for this is that many organisations have learnt the hard way that having a real person as the database owner can lead to trouble. One day that person gets the sack and is thrown out of the AD with a wink of the eye, causing problems on the server instances where he owns databases. (In a large organisation, the DBA may not even be told that this person has been shown the door.) So to avoid this, many DBAs prefer to have an impersonal database owner that cannot disappear, and since sa fulfils that criteria and is always around, it seems like good pick. However, in my opinion it is not. I would like to submit that each database should be owned by a unique SQL login which has no permissions whatsoever and which exists solely for the purpose of owning that database. To make management easier, the name of the SQL login should be tied to the database. Change the connection back to yourself and fix this for Playground:

USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN Playground$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN Playground$owner DISABLE
DENY CONNECT SQL TO Playground$owner
ALTER AUTHORIZATION ON DATABASE::Playground TO Playground$owner

As with the proxy login we created earlier, we work hard to make it difficult to log in with this login. As for the naming convention, feel free to pick your own.

Note: A side effect of this is that when you restore the database to a different server, you must remember to change the database owner to the login Playground$owner on that server, since SQL logins typically have different SID on different servers. Else there will be a mismatch between the owner SID in sys.databases on server level, and the SID for dbo in sys.database_principals, which is likely to cause confusion at some point. A way to avoid this is to use the SID option with CREATE LOGIN to get the same SID on all servers for logins like Playground$owner.

Now change the connection to become Sgt Pepper and try the stunt again:

USE Playground
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT

The output (split up over two lines for legibility):

Sysadmin token_name       type         usage

-------- ---------------- ------------ ------------- 

0        Playground$owner SQL LOGIN    DENY ONLY

0        public           SERVER ROLE  DENY ONLY

0        Playground$owner SQL LOGIN    AUTHENTICATOR

 

   original_login SYSTEM_USER      DBUSER

   -------------- ---------------- ------

   SgtPepper      Playground$owner dbo

   SgtPepper      Playground$owner dbo

   SgtPepper      Playground$owner dbo

This time Sgt Pepper was not able to elevate to sysadmin, because dbo is now the humble Playground$owner. Alas, when Sgt Pepper runs ShowSessions2:

EXEC Management.ShowSessions2

He only sees his own connection. And this is not surprising, given the output above. The usage column reads DENY ONLY. We see Playground$owner listed as AUTHENTICATOR, but it has not been entrusted to authenticate its database users. To this end, it needs to have the permission AUTHENTICATE SERVER. Change the connection to yourself again and run:

USE master
GRANT AUTHENTICATE SERVER TO Playground$owner

Then change the connection back to Sgt Pepper and try ShowSessions2 again:

USE Playground
EXEC Management.ShowSessions2

Success! Sgt Pepper can again see all databases. He tries the dbo stunt once more:

USE Playground
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT

The output this time:

Sysadmin    token_name       type        usage

----------- ---------------- ----------- -------------

0           Playground$owner SQL LOGIN   GRANT OR DENY

0           public           SERVER ROLE GRANT OR DENY

0           Playground$owner SQL LOGIN   AUTHENTICATOR

Good! He is not sysadmin. So, is the privilege-elevation hole tightened? Oh, no. Sgt Pepper has more tricks up his sleeve. In the below, replace LIVERPOOL\GMartin with your credentials:

CREATE USER [LIVERPOOL\GMartin]
EXECUTE AS USER = 'LIVERPOOL\GMartin'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT

Sgt Pepper knows that GMartin is sysadmin, and he creates a user for him in his database, something he has all permissions to do and then he impersonates GMartin. This is the output (broken up on multiple lines for legibility):

Sysadmin token_name        type          usage

-------- ----------------- ------------- --------------

1        LIVERPOOL\GMartin WINDOWS LOGIN GRANT OR DENY

1        public            SERVER ROLE   GRANT OR DENY

1        sysadmin          SERVER ROLE   GRANT OR DENY

1        Playground$owner  SQL LOGIN     AUTHENTICATOR

 

   original_login SYSTEM_USER       DBUSER

   -------------- ----------------- ------

   SgtPepper      LIVERPOOL\GMartin dbo

   SgtPepper      LIVERPOOL\GMartin dbo

   SgtPepper      LIVERPOOL\GMartin dbo

   SgtPepper      LIVERPOOL\GMartin dbo

Sgt Pepper was once more able to elevate to sysadmin. And unless auditing on the server is based on original_login(), risk is considerable that GMartin will get the blame for whatever the Sergeant does. Before we move on, change the connection back to be yourself.

As you have seen, making a database TRUSTWORTHY implies a big security risk. If you make a database TRUSTWORTHY and grant the database owner AUTHENTICATE SERVER, any user in that database who has the permission to create and impersonate users can elevate to sysadmin.

When I suggest that a database should be owned by a non-privileged SQL login, this is an important reason. If the database is owned by sa, a DBA who is in a rush between different tasks can be lured to set a database TRUSTWORTHY, because it sounds so ...innocent. But if he also suggested that he needs to grant a permission, I like to imagine that the gut reaction of most DBAs is that this is maybe something that should not be done casually.

Note: antother situation where you as a DBA might be told that you need to set the database trustworthy is when a developer has an unsafe assembly and has not been able to (read: hasn't care to) sign the assembly with a key. In this case, the database owner needs to be granted permission UNSAFE ASSEMBLY. This has the same consequence: users in that database who have permissions to create assemblies can now elevate to sysadmin, although it takes more skill to exploit than AUTHENTICATE SERVER. In the appendix there is a chapter Loading CLR Objects which discusses loading of assemblies in more detail.

What we have seen explains why we are sandboxed when we use EXECUTE AS USER, be that as a statement or a clause. That is, without the sandbox, users with permissions to create and impersonate users can create a user for some with server-level permission and impersonate that person to elevate their own permissions. Or put in another words: the impersonation of a user should in the normal case only be valid in the realm where the impersonation occurs.

All this said, TRUSTWORTHY is only a security risk if there is a separation of duties between server and database level. On a server which is dedicated to an important tier-one application, there may be no specific users who have elevated permissions on database level only, because the server-level DBAs perform both the server-level and the database-level administration. That is, there is no one who can elevate his permissions. Yet, if you are at such a site and server and consider to make the database TRUSTWORTHY, ask yourself if you believe that this will continue to be true. What if you one day bring in a consultant to work with performance tuning that you make db_owner and grant server permissions needed for monitoring, but you do not want make the consultant sysadmin?

...and meanwhile, Michelle is exercising her procedure-writing skills again:

USE Playground
go
EXECUTE AS USER = 'Michelle'
go
CREATE PROCEDURE #elevate AS
    EXECUTE AS USER = 'LIVERPOOL\GMartin'
    CREATE LOGIN Maxwell WITH PASSWORD = 'SilverHammer'
    ALTER SERVER ROLE sysadmin ADD MEMBER Maxwell
    REVERT
go
EXEC dynamic_playdata2 'zz'' EXEC #elevate --'
go
DROP PROCEDURE #elevate
go
REVERT
DROP LOGIN Maxwell

Not bad of a user without login to elevate to sysadmin!

So that is one more thing to check before you make the database TRUSTWORTHY – no procedures with EXECUTE AS OWNER and SQL injection holes.

It should not come as a surprise that I conclude this section with a strong recommendation against using EXECUTE AS to package server-level permissions. While I prefer certificates for packaging of database permissions, I think EXECUTE AS still is acceptable on this level, if you understand and handle the ramifications for auditing and row-level security. But for server-level permissions, always, always use certificate signing.

Before we move on, turn off the TRUSTWORTHY setting for Playground.

ALTER DATABASE Playground SET TRUSTWORTHY OFF

Server-level Objects

Despite the horrors in the previous section, there is one situation where EXECUTE AS is a fully satisfactory solution and to the degree that it stands out as a the only reasonable choice: server-level triggers that needs elevated permissions. Since they are server-level objects, the EXECUTE AS clause now implies EXECUTE AS LOGIN, so there is no sandboxing and no need for TRUSTWORTHY. And since such triggers are quite confined in what they do, using certificate signing just seems like overkill. (Not the least since because of a disconnect in the syntax, it is not possible to sign server triggers!)

This section has a separate script, 08_logintri.sql.

There are two types of server-level triggers: DDL triggers and logon triggers. We will look at implementing a login trigger that logs all logins to a table in a dedicated database.

WARNING! Before you start this exercise, by all means check that you don't already have a logon trigger on your instance. If you have, you may want to do this exercise on a different instance. Also, since something could go wrong with the logon trigger, don't do this exercise on a server with other users – they may not like being locked out!

First we create a database PlayAudit, and in accordance to what we have said earlier, we create a unique login to be the owner of this database.

USE master
go
CREATE DATABASE PlayAudit
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayAudit$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayAudit$owner DISABLE
DENY CONNECT SQL TO PlayAudit$owner
ALTER AUTHORIZATION ON DATABASE::PlayAudit TO PlayAudit$owner

Next, we create a table to audit all logins on the server in the PlayAudit database:

USE PlayAudit
go
CREATE TABLE loginaudit (
   auditid             bigint        NOT NULL IDENTITY,
   spid                int           NOT NULL
      CONSTRAINT def_loginaudit_spid DEFAULT @@spid,
   username            sysname       NOT NULL
      CONSTRAINT def_loginaudit_username DEFAULT original_login(),
   logintime           datetime2(3)  NOT NULL
      CONSTRAINT def_loginaudit_logintime DEFAULT sysdatetime(),
   appname             sysname       NULL
      CONSTRAINT def_loginaudit_appname DEFAULT app_name(),
   hostname            sysname       NULL
      CONSTRAINT def_loginaudit_hostname DEFAULT host_name(),
   client_host         varchar(48)   NULL
      CONSTRAINT def_loginaudit_client_host DEFAULT
         eventdata().value('(/EVENT_INSTANCE/ClientHost/text())[1]', 'varchar(48)'),
   is_pooled           bit           NULL
      CONSTRAINT def_loginaudit_is_pooled DEFAULT
         eventdata().value('(/EVENT_INSTANCE/IsPooled/text())[1]', 'bit'),
   -- Extra demo columns!
   token_name          sysname       NOT NULL,
   token_type          nvarchar(128) NOT NULL,
   usage               nvarchar(128) NOT NULL,
   CONSTRAINT pk_loginaudit PRIMARY KEY(auditid)
)

The last three columns are from sys.login_token and we will capture them only for the sake for the demo; you would never have them in a regular login-auditing table.

All real auditing columns are defined with defaults, because we know what we want to capture. Some columns are defined as nullable, although we expect them to always have values. One thing to keep in mind here is that if any of the default expressions above would unexpectedly return NULL, do we want the login to succeed anyway? Or do we really want the login to fail? Since the application name and the host name come from the connection string, they are not entirely trustable anyway, so I let them slip. And I am not sure about the ClientHost element from eventdata, so I let it pass as well.

The column is_pooled indicates whether the connection was a brand-new connection or a connection that was re-initiated with sp_reset_connection, that is, reused from the API's connection pool.

The one problem to solve is how the logon trigger will be able to write to the table. We could enable the guest user in PlayAudit and grant guest INSERT permission on the table. But that is an extraordinarily bad idea, and sooner or later you will find Kilroy was here entries, as users could insert rows from SSMS to poke fun with you.

No, the permission to write to the table must be packaged into the trigger, and the table definition is already prepared for this with the use of original_login for the username column. EXECUTE AS serves this purpose well, and given the confined task it seems simplest to use the database owner in the EXECUTE AS clause, although according to the principle of granting minimum permission we should create a specific login. But there should not be any SQL-injection holes in a logon trigger, because there should not be any dynamic SQL at all in a logon trigger. (Logon triggers should be as simple as possible to avoid that they error out and thereby causing users to be locked out.)

Here is the trigger code:

USE master
go
CREATE TRIGGER logintri ON ALL SERVER WITH EXECUTE AS 'PlayAudit$owner' FOR LOGON AS
  INSERT PlayAudit.dbo.loginaudit (token_name, token_type, usage)
     -- DEFAULT VALUES
     SELECT name, type, usage
     FROM   sys.login_token 

The commented line is what you would use if we did not have those columns from sys.login_token for demo purposes.

Once you have the trigger in place, you can try logging in a few times, and if possible, also try from a different machine. Don't forget to test with an unprivileged user like Sgt Pepper. Once you have done this, run:

SELECT * FROM PlayAudit.dbo.loginaudit 

I got this output when I tested. (I've broken up the columns in two segments for readability, and I have shortened the application name for Management Studio to save space):

auditid spid username   logintime                appname      hostname

------- ---- ---------- ------------------------ ------------ -----------

1       51   SgtPepper  2017-08-16 22:10:50.312  SQLCMD       RC2

2       51   SgtPepper  2017-08-16 22:10:50.312  SQLCMD       RC2

3       51   SgtPepper  2017-08-16 22:10:58.117  SSMS - Query LIVERPOOL

4       51   SgtPepper  2017-08-16 22:10:58.117  SSMS - Query LIVERPOOL

5       53   SgtPepper  2017-08-16 22:10:58.152  SSMS - Query LIVERPOOL

6       53   SgtPepper  2017-08-16 22:10:58.152  SSMS - Query LIVERPOOL

 

client_host      is_pooled token_name        token_type   usage

---------------- --------- ----------------- ------------ -------------

192.168.145.139  0         PlayAudit$owner   SQL LOGIN    GRANT OR DENY

192.168.145.139  0         public            SERVER ROLE  GRANT OR DENY

<local machine>  0         PlayAudit$owner   SQL LOGIN    GRANT OR DENY

<local machine>  0         public            SERVER ROLE  GRANT OR DENY

<local machine>  0         PlayAudit$owner   SQL LOGIN    GRANT OR DENY

<local machine>  0         public            SERVER ROLE  GRANT OR DENY

The most interesting columns in the context of this article are the last three. We can see that there are two server tokens: PlayAudit$owner and public. The usage for both is GRANT OR DENY, that is, there is no sandboxing, but the trigger can access the PlayAudit database.

Note: when you test by logging in from SSMS, you may be puzzled to see two connections directly after each other. SSMS makes an extra connection behind your back for IntelliSense, even if you have turned it off.

I would recommend that before you move on that you drop the logon trigger:

DROP TRIGGER logintri ON ALL SERVER

Other Side Effects of Impersonation

This section briefly covers how impersonation inside SQL Server affects the world outside of it.

Just like impersonation on database level is by default not trusted on server level, impersonation in SQL Server is not trusted in Windows and the world outside. The reason is obvious: a DBA should not be able to elevate his permissions to be Administrator in Windows by impersonating a Windows administrator. Some operations in SQL Server that interact with the outside world impersonates your login if you are logged in with integrated security. (Which is possible, since Windows has authenticated you and SQL Server got your token when you connected to SQL Server.) These operations will fail, if you have impersonated a Windows login inside SQL Server. One such example is linked servers, something that I discuss more in detail in the appendix, in the chapter Controlling Access to Linked Servers.

SQL Server MVP Adam Machanic made me aware of a case that relates to CLR modules. If your CLR module makes some external access outside SQL Server you may want to use the security context of the user who is currently logged into SQL Server, rather than the service account for SQL Server. This is only possible if the user is logged in with integrated authentication. To this end you can retrieve the object SqlContext.WindowsIdentity, which has a number of properties and methods, including one that permits you impersonate the user. But if there is an EXECUTE AS somewhere on the call stack, SqlContext.WindowsIdentity will return null in most cases. (There are some situations where it will return the identity of the service account for SQL Server, which you don't have much use for.)

Cross-Database Access

The last area that we will look at this article is how to handle permissions for cross-database access. This is quite a complex area, because the scenarios where you want to access in a different database can be vastly different. Consider these two extremes:

  1. Two different applications that have a limited need to exchange data. For instance, there may be a single place where the Payroll database needs to access the HR database.
  2. An application that for whatever reason is distributed over multiple databases and there are cross-database queries all over the place.

Your real-world situation may fall somewhere in between or be more specific. There is not really any solution that is best for all scenarios. For this reason, we will look at five different options for handling permissions cross-database access:

  1. Grant direct permissions to objects in the other database.
  2. Enabling the guest user.
  3. Certificate signing.
  4. Enable cross-database ownership chaining.
  5. EXECUTE AS + TRUSTWORTHY.

Certificate signing is very well apt for the first scenario above, but you are likely to find it impractical in the second where ownership chaining and EXECUTE AS are better fits. The first option may be your preference for some scenarios that fall between the extremes. Enabling the guest user is the least recommendable option, but there are a few specialised scenarios where it makes sense.

The script for this chapter is in 09_crossdbaccess.sql.

Granting Direct Permissions

For these exercises we need a new test user, one that has a server-level login, but who has no elevated permissions anywhere. Let me introduce Lovely Rita, a traffic warden by profession. We will create a login for her and make her a user in Playground and a member of Playrole:

SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE LOGIN Rita WITH PASSWORD = 'MeterMaid'
go
USE Playground
go
CREATE USER Rita
ALTER ROLE Playrole ADD MEMBER Rita

To test cross-database access, we also need to create another database. In fact, we will create one or more for most techniques we will look at. For this section we will work with the database DirectPlay and we create it according to the practice we have established. (You may sense that this is a perfect candidate for automation through a stored procedure or even better a PowerShell script you can run on any server.)

USE master
go
CREATE DATABASE DirectPlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN DirectPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN DirectPlay$owner DISABLE
DENY CONNECT SQL TO DirectPlay$owner
ALTER AUTHORIZATION ON DATABASE::DirectPlay TO DirectPlay$owner

We also need a table in this database to play with and while we're at it, we add a stored procedure to the database and add some data to the table.

USE DirectPlay
go
CREATE TABLE Directtable (id       int          NOT NULL,
                          somedata nvarchar(40) NOT NULL,
                          whodidit sysname      NOT NULL
                               CONSTRAINT Direct_default_whodidit DEFAULT USER,
                           CONSTRAINT pk_Direct PRIMARY KEY (id)
)
go
CREATE PROCEDURE add_directdata @id int, @somedata nvarchar(40) AS
   INSERT Directtable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM Directtable WHERE id = @id
go
EXEC add_directdata 1, 'The Long and Winding Road'

We move over to Playground, where we make some attempts to permit Rita to work with Directtable.

USE Playground
go
CREATE PROCEDURE get_direct_data @id int AS
   SELECT id, somedata, whodidit
   FROM   DirectPlay.dbo.Directtable
   WHERE  id = @id
go
EXECUTE AS LOGIN = 'Rita'
EXEC get_direct_data 1
go
REVERT

This is the error message:

Msg 916, Level 14, State 1, Procedure get_direct_data, Line 2

The server principal "Rita" is not able to access the database "DirectPlay" under the current security context.

Which is not very surprising, since Rita is not a user in DirectPlay. As long we don't have any other arrangements in place, the users in Playground who need to access data in DirectPlay, must also be users in DirectPlay, so let's address that.

USE DirectPlay
go
CREATE ROLE PlaygroundUsers
CREATE USER Rita
ALTER ROLE PlaygroundUsers ADD MEMBER Rita

As it can be practical to know which users in DirectPlay that are guests from Playground, we create a role for them, and this also permits us to tailor the permissions for these people. We add Rita to the role, and she makes a new attempt:

USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC get_direct_data 1
go
REVERT

There is no success, but the error message is different:

Msg 229, Level 14, State 5, Procedure get_direct_data, Line 2

The SELECT permission was denied on the object 'Directtable', database 'DirectPlay', schema 'dbo'.

There is no ownership chaining, so there is a permission check, and since PlaygroundUsers has not been granted any permission, the check fails.

To overcome this, we would have to grant SELECT permission on some level to PlaygroundUsers. But wait a minute! Assume that DirectPlay is like Playground, that is, the regular users of DirectPlay only get EXECUTE permissions on the dbo schema. Why would we all of a sudden grant these Playground people extra permissions? After all, there is no guarantee that they will always come in from Playground – there is nothing to stop them from connecting directly to DirectPlay.

That is, if the Playground application needs to access DirectPlay, that should preferably be through stored procedures. And to keep the users of Playground who are not regular users of the DirectPlay application out from the rest of the DirectPlay, we should put these stored procedures in a separate schema:

USE DirectPlay
go
CREATE SCHEMA PlaygroundGuests
go
GRANT EXECUTE ON SCHEMA::PlaygroundGuests TO PlaygroundUsers
go
CREATE PROCEDURE PlaygroundGuests.get_direct_data @id int AS
   SELECT id, somedata, whodidit
   FROM   dbo.Directtable
   WHERE  id = @id

In the Playground database, you change the get_direct_data procedure to read:

USE Playground
go
ALTER PROCEDURE get_direct_data @id int AS
   EXEC DirectPlay.PlaygroundGuests.get_direct_data @id
Rita can now run the procedure successfully:
EXECUTE AS LOGIN = 'Rita'
EXEC get_direct_data 1
go
REVERT

Let's now assess this solution. We can see that there are two potential pain points:

  1. A lot of extra administration to add Playground users to DirectPlay.
  2. It's cumbersome if you want to join tables in the two databases, if all you have is an SP interface.

I say "potential", because it depends on the actual situation. When it comes to the first point, it could be that people generally are users of both applications, and in that case there is no point with the extra PlaygroundGuests schema. Or the case might that only one or two of the users in Playground need to run the procedure in DirectPlay.

When it comes to the second point, you may be in the lucky situation where granting SELECT permission to PlaygroundGuests is entirely permissible. This way the problem vanishes, at least as long as there is no need for PlaygroundGuests to update data in DirectPlay with data from Playground. There may also be middle grounds, where DirectPlay data is exposed through views or table-valued functions in a controlled way and PlaygroundUsers are granted access on these views. Or it may be feasible to capture the data from DirectPlay in OUTPUT parameters or in a temp table with INSERT-EXEC and use that for further processing.

But you can definitely run into situations where not being able to join tables in the two databases freely is a serious limitation. In that case, you need to find something else than this simple-minded solution. The next solution has the same issue, but the last three remove the second pain point, as they all in their own way permit you to freely write joins across the databases within a stored procedure without granting direct access to the tables.

Enabling the guest User

This can be seen as a variation of the previous solution. Instead of adding users from the source database as users in the target database, you simply say:

GRANT CONNECT TO guest

Now anyone on the server can access the database, and if they don't have a user of their own in the database, they will map to the guest user and any permissions they need you have to grant to guest.

While this relieves you of having to administer users, it also deprives you of any control who can access the database, so you should only do this if what you really want is "anyone on the server should be able to access this database". A possible case is a database with reference data that freely can be used from any application.

A more elaborate scenario is a server that hosts a multi-tenant application with one database per tenant. The tenant users can only access their databases through a web UI and they have no access to SQL Server directly. The application has a common database with not only reference data, but also common utility stored procedures / functions and tables where you record billing or telemetry information. The tenants administer their users themselves through the web UI, but you don't want to have all the tenant users in that common database. A simple solution is to enable the guest user and be done with it. I will return to this scenario in the last section of this chapter for a different approach.

But apart from a few special situations, enabling guest is definitely considered bad practice and nothing you should do, because normally, you do want to control who can access a certain database. That said, of the four system databases, three – master, tempdb and msdb – all have guest enabled, and you would cause yourself some problems if you disabled guest in any of them. (And, in fact, SQL Server will not even permit you when it comes to master and tempdb.)

Certificate Signing

Let's now look at how certificate signing can be used to permit cross-database access. Before we look at the theory, let's set up a new database to test in. The initial setup is the same as for DirectPlay. That is, we have a dedicated owner, and there is a table with a stored procedure to add data to it. Note that no users are added.

USE master
go
CREATE DATABASE PlaySign
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlaySign$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlaySign$owner DISABLE
DENY CONNECT SQL TO PlaySign$owner
ALTER AUTHORIZATION ON DATABASE::PlaySign TO PlaySign$owner
go
USE PlaySign
go
CREATE TABLE Signtable (id       int          NOT NULL,
                        somedata nvarchar(40) NOT NULL,
                        whodidit sysname      NOT NULL
                             CONSTRAINT Sign_default_whodidit DEFAULT USER,
                        CONSTRAINT pk_Sign PRIMARY KEY (id)
)
go
CREATE PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
   INSERT Signtable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM Signtable WHERE id = @id
go
EXEC add_signdata 1, 'Back in the USSR'

Say now that we want to permit users in the Playground database to add data to Signtable through a stored procedure in the Playground database. We want them to use a certain id range that we have set aside for them, and we enforce that in the procedure:

USE Playground
go
CREATE PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
   DECLARE @low  int = 20000,
           @high int = 15000

   IF @id BETWEEN @high AND @low
   BEGIN
      INSERT PlaySign.dbo.Signtable(id, somedata)
         VALUES(@id, @somedata)

      SELECT id, somedata, whodidit
      FROM   PlaySign.dbo.Signtable
      WHERE  id = @id
   END
   ELSE
      RAISERROR('Illegal id. The permitted range is %d - %d', 16, 1, @low, @high)

The recipe for packaging cross-database access in a stored procedure with certificate signing is very similar to when packaging server-level permissions. That is:

  1. Create a certificate in the target database, that is, PlaySign in this example.
  2. Create a user from the certificate.
  3. Grant the user the permissions needed.
  4. Export the certificate.
  5. Import the certificate into the originating database.
  6. Sign the procedure in question.
  7. Optional: drop the private key.

Since we have looked at this step by step in the past, I take the liberty to run all steps in one go this time:

USE PlaySign
go
CREATE CERTIFICATE [SIGN Playground.dbo.add_signdata]
ENCRYPTION BY PASSWORD = 'Lucy in the Sky with Diamonds'
WITH SUBJECT = 'GRANT INSERT SELECT ON Signtable'
go
CREATE USER [SIGN Playground.dbo.add_signdata] 
    FROM CERTIFICATE [SIGN Playground.dbo.add_signdata]
GRANT INSERT, SELECT ON dbo.Signtable TO [SIGN Playground.dbo.add_signdata]
go
DECLARE @cert_id int = cert_id('[SIGN Playground.dbo.add_signdata]')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'Lucy in the Sky with Diamonds',
              'Lucy in the Sky with Diamonds')

SELECT @cert_id, @public_key, @private_key

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE [SIGN add_signdata for SignPlay]
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''Lucy in the Sky with Diamonds'',
          ENCRYPTION BY PASSWORD = ''Lucy in the Sky with Diamonds'')'

PRINT convert(varchar(MAX), @sql)

EXEC Playground.sys.sp_executesql @sql
go
ALTER CERTIFICATE [SIGN Playground.dbo.add_signdata] REMOVE PRIVATE KEY
go
USE Playground
go
ADD SIGNATURE TO dbo.add_signdata BY CERTIFICATE [SIGN add_signdata for SignPlay]
   WITH PASSWORD = 'Lucy in the Sky with Diamonds'
go
-- ALTER CERTIFICATE [SIGN add_signdata for SignPlay] REMOVE PRIVATE KEY

Note here that in this case I have opted to name the certificate differently in the two databases in order to convey the relevant information to the respective power users in the databases. I have commented out the statement to remove the private key from Playground of the simple reason that we will change the procedure later and we will thus need to re-sign the procedure.

Rita gives it a try:

USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15001, 'You have a parking ticket!'
go
REVERT

(Yes, she is lovely, didn't I say that?) Success, the row is inserted and she can read the inserted row.

id      somedata                     whodidit

------- ---------------------------- -----------

15001   You have a parking ticket!   Rita

And note here: Rita has no permission on Signtable – in fact, she is not even added as a user in the PlaySign database. How did this work out? Let's look at the user tokens, in both databases:

USE Playground
go
ALTER PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
   DECLARE @low  int = 20000,
           @high int = 15000

   SELECT name, type, usage FROM sys.user_token
   SELECT name, type, usage FROM PlaySign.sys.user_token

   IF @id BETWEEN @high AND @low
   BEGIN
      INSERT PlaySign.dbo.Signtable(id, somedata)
         VALUES(@id, @somedata)

      SELECT id, somedata, whodidit
      FROM   PlaySign.dbo.Signtable
      WHERE  id = @id
   END
   ELSE
      RAISERROR('Illegal id. The permitted range is %d - %d', 16, 1, @low, @high)

Let's first see what we have before we add any signature the procedure:

EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15002, 'No, Sir, you cannot park here'
go
REVERT

This is the output:

name           type       usage

-------------- ---------- --------------

Rita           SQL USER   GRANT OR DENY

public         ROLE       GRANT OR DENY

Playrole       ROLE       GRANT OR DENY

 

Msg 916, Level 14, State 1, Procedure add_signdata, Line 6

The server principal "Rita" is not able to access the database "PlaySign" under the current security context.

You are probably not surprised to see that Rita cannot see user tokens in a database she does not have access to. Now, let's sign the procedure and try again:

ADD SIGNATURE TO dbo.add_signdata BY CERTIFICATE [SIGN add_signdata for SignPlay]
   WITH PASSWORD = 'Lucy in the Sky with Diamonds'
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15002, 'No, Sir, you cannot park here'
go
REVERT

This is the output:

name                               type                        usage

---------------------------------- --------------------------  -------------

Rita                               SQL USER                    GRANT OR DENY

public                             ROLE                        GRANT OR DENY

Playrole                           ROLE                        GRANT OR DENY

 

name                               type                        usage

---------------------------------- --------------------------- -------------

public                             ROLE                        GRANT OR DENY

SIGN Playground.dbo.add_signdata   USER MAPPED TO CERTIFICATE  GRANT OR DENY

 

id          somedata                        whodidit

----------- ------------------------------- -----------

15002       No, Sir, you cannot park here   Rita

You can see that the tokens from Playground are the same as when the procedure was unsigned. But now we also see two tokens from PlaySign. There is the omnipresent public role and then there is the certificate user which contributes with three permissions. That is, beyond the INSERT and SELECT permissions that we granted explicitly, it also has CONNECT permission on the database as this is automatically granted when you create a user with CREATE USER. You can verify this by revoking that permission and try again:

USE PlaySign
go
REVOKE CONNECT FROM [SIGN Playground.dbo.add_signdata]
go
USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15003, 'When I am free to take some tea with you???'
go
REVERT

The error message is now the same as when the procedure was not signed at all.

All and all, certificate signing is a very palatable solution in the case you have an occasional cross-database access between databases that are part of different applications, and of which the user base is largely disjunct. The access from Playground can be constrained to procedures that have been signed with certificates from PlaySign and where each certificate user has been granted the exact permissions needed for the procedure in question.

It is interesting to note that this can be used without full trust between the power users in the two databases. Say that Signe is the power used of the PlaySign database. She runs the first steps of the recipe in her own database. She then creates a script with the statements to be run in the Playground database and saves it on a USB stick. She walks over to Sgt Pepper's desk. She can review the procedure to make sure that it performs the actions that she and Sgt Pepper have agreed on. She inserts the USB stick, and she monitors Sgt Pepper as he runs her script and particularly, she makes sure that the private key is dropped. That is, there is no way that Sgt Pepper can change the procedure without her approval.

It is also worth noticing that a property of this solution is that Signe and Sgt Pepper can make these arrangements on their own with involving the server-level DBA. This is not true for the two remaining solutions.

If you have a multi-database application where there are cross queries all over the place, it is likely that you will find certificates to be a bit too much hassle. Even if you can mitigate the situation with good automation, it will have to be admitted that certificates are good for the few and exceptional cases, but if you need to sign everything to handle permissions it gets a little out of hand.

Ownership Chaining

Let it be said directly: this method opens a security hole that permits a local power user to elevate his permission to sysadmin, although the way to do it is a little more complicated and roundabout than when the database is set to TRUSTWORTHY. There may be a way to close the hole, but it is of dubious nature and may cause other things to break. More about later. I will first talk about the method as such.

By default, ownership chaining does not work across databases, but it can be enabled. There are two ways to do this. You can enable it for the entire server with the configuration parameter cross db ownership chaining, and then it applies to all databases. I recommend against using this option. It is much better to enable cross-database chaining only for the databases that need it, and this is what we will work with in this section.

To play with ownership chaining, we create two databases, but there is one difference from how we have created databases previously:

USE master
go
CREATE DATABASE PlayChain1
CREATE DATABASE PlayChain2
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayChain$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayChain$owner DISABLE
DENY CONNECT SQL TO PlayChain$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain1 TO PlayChain$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain2 TO PlayChain$owner

We have established the principle that each database should be owned by a SQL login, existing to own only that database. However, if you have a multi-database application, it can make sense to have the same owner for all databases that belong to the application. Even more so, if you wish to employ ownership chaining across the databases.

Next, we set things up in the databases. Rita needs to be present in both databases, this is a requirement when you rely on ownership chaining. (But since the assumption is that the databases are for the same application, this is something you would most likely have to do anyway.) We create a table in one of the databases, and a stored procedure to access the table in the other database:

USE PlayChain1
go
CREATE USER Rita
go
CREATE TABLE Chaintable (id       int          NOT NULL,
                         somedata nvarchar(40) NOT NULL,
                         whodidit sysname      NOT NULL
                              CONSTRAINT Chain_default_whodidit DEFAULT USER,
                         CONSTRAINT pk_Chain PRIMARY KEY (id)
)
go
USE PlayChain2
go
CREATE USER Rita
CREATE ROLE ChainUsers
ALTER ROLE ChainUsers ADD MEMBER Rita
GRANT EXECUTE ON SCHEMA::dbo TO ChainUsers
go
CREATE PROCEDURE add_chaindata @id int, @somedata nvarchar(40) AS
   INSERT PlayChain1.dbo.Chaintable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM PlayChain1.dbo.Chaintable WHERE id = @id

You may note that in PlayChain1, we did not set up any permissions for Rita, only in PlayChain2.

Rita tries to enter a parking ticket in Chaintable with help of the stored procedure:

USE PlayChain2
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_chaindata 1, 'Parking in the middle of a roundabout?'
go
REVERT

But this fails:

Msg 229, Level 14, State 5, Procedure add_chaindata, Line 2

The INSERT permission was denied on the object 'Chaintable', database 'PlayChain1', schema 'dbo'.

add_chaindata and Chaintable have the same owner, PlayChain$owner, but since cross-database ownership chaining is not enabled, there is still a permission check.

To enable ownership chaining, the option DB_CHAINING must be set for both databases:

ALTER DATABASE PlayChain1 SET DB_CHAINING ON
ALTER DATABASE PlayChain2 SET DB_CHAINING ON

While many database options only require membership in db_owner to be changed, DB_CHAINING is like TRUSTWORTHY: it requires that you have CONTROL SERVER. That is, if you are administrator only on database level, you need to talk to the server-level DBA to enable cross-database chaining for your database. (Who, for reasons that will prevail, have all reasons to say no.)

Rita can now issue her parking ticket:

USE PlayChain2
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_chaindata 1, 'Parking in the middle of a roundabout?'
go
REVERT

The output:

id     somedata                                 whodidit

------ ---------------------------------------- -----------

1      Parking in the middle of a roundabout?   Rita

Once the two databases have been enabled for cross-database chaining, you can develop for the two-database application just like you do for the single-database application. That is, you rely on ownership chaining for the plain vanilla stuff, and if you need dynamic SQL or something else where ownership chaining does not cut it, you use certificate signing in those cases.

As I mentioned, there is a security problem with enabling cross-database ownership chaining. In fact, there are several. Or rather, there are several ways that cross-database ownership chaining can lead to exploits.

Let's first look at what can happen if chaining is enabled for unrelated databases. Say that Sgt Pepper is a regular user of the Chain application:

USE PlayChain1
go
CREATE USER SgtPepper
go
USE PlayChain2
go
CREATE USER SgtPepper
ALTER ROLE ChainUsers ADD MEMBER SgtPepper

There is still no cause for concern, but assume now that Sgt Pepper persuades the server-level DBA that his database also needs to have DB_CHAINING enabled:

ALTER DATABASE Playground SET DB_CHAINING ON

Sgt Pepper then writes this stored procedure in is own database:

USE Playground
go
CREATE PROCEDURE my_add_chaindata @id int, @somedata nvarchar(40) AS
   INSERT PlayChain1.dbo.Chaintable(id, somedata, whodidit)
      VALUES(@id, @somedata, 'Kilroy was here!')
   SELECT id, somedata, whodidit FROM PlayChain1.dbo.Chaintable WHERE id = @id

He tries to run his procedure:

EXECUTE AS LOGIN = 'SgtPepper'
EXEC my_add_chaindata 2, 'With Love from Me to You'
go
REVERT

But there is a permission error:

Msg 229, Level 14, State 5, Procedure my_add_chaindata, Line 2

The INSERT permission was denied on the object 'Chaintable', database 'PlayChain1', schema 'dbo'.

Both my_add_chaindata and Chaintable are owned by dbo – but it is not the same dbo, so there is no ownership chaining. However, we have already seen that the not-so-good old Sergeant is not one who gives up that easily. Being the administrator and power user for Playground, he creates a user for PlayChain$owner in his database and then makes PlayChain$owner owner of my_add_chaindata. Now he runs the procedure again:

EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER PlayChain$owner
ALTER AUTHORIZATION ON my_add_chaindata TO PlayChain$owner
EXEC my_add_chaindata 2, 'With Love from Me to You'
go
REVERT

Now that the procedure and the table have the same owner, ownership chaining applies and his plot succeeds:

id    somedata                            whodidit

----- ----------------------------------- ----------------

2     With Love from Me to You            Kilroy was here!

That is, casual use of DB_CHAINING can lead to vulnerabilities between user databases, and that is why it is not a good idea to use the configuration parameter to turn on cross-database ownership chaining on instance level.

But that is only the start. Of the system databases, master, tempdb and msdb all have DB chaining enabled by default. Furthermore, as we learnt in the previous section, the guest user is enabled in these databases, so all users have access to them. This can be exploited by anyone with database powers to create users and store procedures in a user database enabled for DB chaining. Here is an example of how.

Sgt Pepper starts off with writing a procedure that lists the contents in the sysjobs table in msdb and tries to run it:

USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE PROCEDURE list_jobs AS
    SELECT * FROM msdb.dbo.sysjobs
go
EXEC list_jobs
go
REVERT

This first attempt results in an error message:

Msg 229, Level 14, State 5, Line 322

The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

There is a permission error, but we can note that Sgt Pepper is able to access the database; this is because the guest user has been granted CONNECT permission in msdb.

But what if Sgt Pepper tries the trick above, and creates a user for the owner of msdb in Playground and then makes that user the owner of list_jobs?

The owner of msdb is sa. So:

EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER sa
go
REVERT

The result is negative:

Msg 15405, Level 16, State 1, Line 339

Cannot use the special principal 'sa'.

There seems to be a road block. But we know that the Sergeant is the creative sort of person. Maybe this works:

EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER my_sa FOR LOGIN sa
go
REVERT

The error message is the same. However, what about creating a user for some other SQL login and then remap that user with ALTER USER?

EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER my_sa FOR LOGIN DirectPlay$owner
ALTER USER my_sa WITH LOGIN = sa
go
REVERT

Yup! That worked out. Sgt Pepper can now complete his plot:

EXECUTE AS LOGIN = 'SgtPepper'
go
ALTER AUTHORIZATION ON list_jobs TO my_sa
EXEC list_jobs
go
REVERT

Ownership chaining permits him to list the jobs in msdb. Obviously, this is quite harmless, but if he can write stored procedures that runs SELECT on sysjobs and related tables through ownership chaining, he can write stored procedures insert and modify data into these tables. That is, he can add and modify jobs. For instance, he can add a job that makes him sysadmin. Or add a job that perform tasks for him as sysadmin.

Can you protect yourself against this attack, by turning off DB_CHAINING for msdb or disable the guest user? I was able to drop guest from msdb, although Books Online says that you cannot do that. However, it is a clear hint that you should not. And, indeed, when I tried to backup a database as a non-sysadmin user I got permission errors when BACKUP tried to record the backup in msdb. (The backup as such was successful anyway.) What breaks if you disable ownership chaining, I have not been able to figure out, but I am not brave enough to recommend you to turn off ownership chaining for msdb. That would have to be your own decision.

As I mentioned, DB_CHAINING is also enabled by default for tempdb and master, and SQL Server does not permit you to change the setting for these databases. Can they be exploited for something? This query returns objects in master to which public has not been granted permission:

USE master
go
SELECT *
FROM   sys.all_objects o
WHERE  NOT EXISTS (SELECT *
                   FROM   sys.database_permissions dp
                   WHERE  dp.major_id = o.object_id
                     AND  dp.class = 1
                     AND  dp.grantee_principal_id = 0)

It returns over 150 objects. Of these, the major part is in the sys schema, and it seems that these are in fact in the resource database, and when attempting to access them with the trick above, I still get a permission error. A few objects are in the dbo schema, but they seem fairly innocent. But I am not going to vouch for that there are no vulnerabilities with regards to master. And for that matter, you may have your own private objects in master.

Note: If you think renaming sa is an option, it is not. Any user who wishes to know what sa has been renamed to can use suser_name(1) to find out. Nor does it help to disable sa as a login.

Does this mean that cross-database ownership chaining is just as bad as TRUSTWORTHY + AUTHENTICATE SERVER and should not be used?

Yes and no. If you are looking for real security and you have no trust in the persons who are power users in the database, there is definitely no difference. On the other hand, if you trust these people not to be rogue, but you only want to keep them out of sysadmin to avoid that they cause accidental damage, I think there is a difference. Making a database trustworthy more or less invites them to use EXECUTE AS if they need some server-level thing done quickly and you are in a meeting. The hurdle of going through a job is certainly higher.

And of course there is always the situation that there are no local power users. That is, everyone who has elevated permission in the database are already sysadmin, so no one can elevate and enabling ownership chaining is harmless.

You will have to make your own assessments to decide whether enabling cross-database ownership chaining is acceptable for you, but you have seen all the caveats and you should be aware of them.

EXECUTE AS + TRUSTWORTHY

The EXECUTE AS clause can be used to implement cross-database access, but before you consider this option, there are two initial conditions that needs to be fulfilled:

  1. You have firmly established the principle that user databases are owned by dedicated SQL logins with no server-level permissions.
  2. The application uses original_login or session context for auditing and row-level security.

You will also need to consider the risk for permission elevation across databases, and I will discuss this just below.

For EXECUTE AS to work, the originating database must have been marked as TRUSTWORTHY, and the owner of the originating database must have been granted AUTHENTICATE permission in the target database. The latter is a trivial matter, if the databases have the same owner.

EXECUTE AS + TRUSTWORTHY is an mainly an option for a something like a multi-database application. I don't think it is a good choice for occasional cross access between unrelated applications, like the Payroll database having the need to access the HR database in a single place. For one thing, most likely there are power users in Payroll who are not power users (or even users) in HR. If Payroll is marked TRUSTWORTHY, and the owner of Payroll is granted AUTHENTICATE in HR, these users can access the HR database with db_owner rights, in the same way that Sgt Pepper previously elevated to sysadmin when we made Playground TRUSTWORTHY. On the other hand, in a multi-database application where the set of power users is the same in all databases, this not a concern. (Please pay attention the emphasis.)

EXECUTE AS certainly have a couple of advantages over cross-database ownership chaining:

  1. There is no risk for permission elevation through msdb.
  2. You may not have to add users to all databases. Say that all stored procedures that are accessed directly by the application are in one database. In this case, you only need to administer users in this first database.
  3. It permits for asymmetric cross-access where DB1 can access DB2, but not vice versa. Let's look at the scenario with the multi-tenant application again. There is one database per tenant and a common database with various support objects. The tenants access the application over the web without direct access to SQL Server. The only power users of the tenant databases are the server-level DBA staff. There are other people in your staff that have access to the common database to maintain data in the lookup tables etc. Some of them may also have rights to write stored procedures to report off the tracking tables. However, these people should not be permitted to see the data of the tenants, and therefore you cannot enable cross-database ownership chaining on the tenant databases. As discussed previously, one solution is to enable guest for this common database. However, this breaks down if you add some other unrelated database on the server. For this reason, your preference might be to mark each tenant database as trustworthy, and grant each database owner AUTHENTICATE permission in the common database and use EXECUTE AS in all procedures that access the common database.
  4. You need to perform operations to which ownership chaining does not apply, for instance dynamic SQL, create users, metadata access etc. This is not really any different from similar operations within a database. If this is your sole concern with ownership chaining and it is only a matter for a handful procedures, certificate signing is still an option.

When I introduced EXECUTE AS to package permissions, I said that you should create a proxy user for each procedure and grant this user permissions tailored for the procedure in question. Admittedly, this model is not really practical for an application with cross-database references all over the place. If you have to update the permission for the proxy user every time you change a procedure to reference a new table, this becomes a bit of a burden.

But I would really like to discourage you from using EXECUTE AS OWNER across the board, given the risks this incurs if there are SQL-injection holes. At a minimum have one proxy user for read-only access and another for write access. For procedures that use dynamic SQL consider to use tailored proxy users for these.

You may also recall that I suggested that you should create these proxy users WITHOUT LOGIN. You may think that for cross-database access, you need to create actual logins and create users for them in each database. But that is not necessary. You can create the same login-less user in two databases, as long as you as you make sure they have the same SID. (To be precise: you can do this on SQL 2012 or later. The option to specify a SID with CREATE USER is not available in SQL 2008.)

Here is an example that brings everything I've talked of so far together. Let's first create some databases.

USE master
go
CREATE DATABASE ImpersonalPlay1
CREATE DATABASE ImpersonalPlay2 WITH TRUSTWORTHY ON
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN ImpersonalPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN ImpersonalPlay$owner DISABLE
DENY CONNECT SQL TO ImpersonalPlay$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain1 TO ImpersonalPlay$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain2 TO ImpersonalPlay$owner

As with PlayChain1 and PlayChain2, the databases have the same owner; we assume they are part of the same application. We mark ImpersonalPlay2 as TRUSTWORTHY, but not ImpersonalPlay1.

Let's now prepare things in ImpersonalPlay1:

USE ImpersonalPlay1
go
CREATE TABLE ImpersonalTable
             (id       int          NOT NULL,
              somedata nvarchar(40) NOT NULL,
              whodidit sysname      NOT NULL
                 CONSTRAINT Impers_default_whodidit DEFAULT original_login(),
              CONSTRAINT pk_impersonal PRIMARY KEY (id)
)
go
CREATE USER ImpersonalReader WITHOUT LOGIN
CREATE USER ImpersonalWriter WITHOUT LOGIN
ALTER ROLE db_datareader ADD MEMBER ImpersonalReader
ALTER ROLE db_datareader ADD MEMBER ImpersonalWriter
ALTER ROLE db_datawriter ADD MEMBER ImpersonalWriter

We create our standard test table, but note that the default of the auditing column is different so that it will work with impersonation. We also create two login-less users that we add to db_datareader and db_datawriter explicitly. (ImpersonalWriter is added to both roles, since db_datawriter does not give SELECT permission, and most likely that will be needed also in updating procedures.) You may also note that we do not add Rita as a user in this database.

Now over to ImpersonalPlay2 where we first create the proxy users:

USE ImpersonalPlay2
go
DECLARE @sql nvarchar(MAX)
SELECT @sql = (SELECT 'CREATE USER ' + quotename(name) + ' WITHOUT LOGIN ' +
                      'WITH SID = ' + convert(varchar(200), sid, 1) +
                      char(13) + char(10)
               FROM   ImpersonalPlay1.sys.database_principals
               WHERE  name LIKE 'Impersonal%'
                 AND  type = 'S'
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
PRINT @sql
EXEC(@sql)

That is, we get the SIDs from the first database by running a query against sys.database_principals. And then we embed it in the FOR XML mumbo-jumbo to build a concatenated string to produce the CREATE USER commands for this database. When I ran this, I got this output:

CREATE USER [ImpersonalReader] WITHOUT LOGIN WITH SID = 0x010500000000000903000000F856ED49191FD74B858CCC9C6AD1E798

CREATE USER [ImpersonalWriter] WITHOUT LOGIN WITH SID = 0x010500000000000903000000256621A8D12810439E3C83D8862DF6FA

We go on with adding Rita as a user and our standard user role:

CREATE USER Rita
CREATE ROLE ImpersonalUsers
ALTER ROLE ImpersonalUsers ADD MEMBER Rita
GRANT EXECUTE ON SCHEMA::dbo TO ImpersonalUsers

You may note that we don't add the proxy users to any roles in ImpersonalPlay2, as they exist in this database only to appear in EXECUTE AS clauses. (But if we would join tables in ImpersonalPlay1 and ImpersonalPlay2 to each other, it would be a different matter.)

We create our standard procedure, this time with an EXECUTE AS clause:

CREATE PROCEDURE add_impersonaldata @id int, @somedata nvarchar(40)
   WITH EXECUTE AS 'ImpersonalWriter' AS
   INSERT ImpersonalPlay1.dbo.ImpersonalTable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit
   FROM   ImpersonalPlay1.dbo.ImpersonalTable
   WHERE id = @id

Rita is now ready to try the procedure:

USE ImpersonalPlay2
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_impersonaldata 2, 'We need to tow this car away'
go
REVERT

The row is inserted successfully. The column whodidit does not say Rita, though, but that was only because we impersonated her through EXECUTE AS LOGIN to make testing easier. You could login directly with Rita (password Meter Maid), if you want to see that whodidit is updated correctly.

Conclusion on Cross-Database Access

We have now looked at five ways to implement cross-database access. All have their pros and cons, and which is the best for you depends on your context. I have tried to capture some scenarios that I believe are typical, but yours may not resemble of any of them. In that case, you will have to use your own good judgement to decide what is best for you.

Conclusion and Acknowledgements

We are coming to the end of the main article. There is still an appendix where I elaborate on how you can use the techniques I have discussed here in various specific situations. In this article we have look at three ways to package permissions in a stored procedure. Here is a quick summary of their properties:

Ownership Chaining

Certificate Signing

EXECUTE AS

If you have done all the demos in this article, you now have quite a few databases, logins etc on your server. You can use the script CleanupAll.sql to drop all the objects created by the scripts. Please review and edit the clean-up script before you run it, if there is anything you want to keep. (But whatever you do, don't drop the PlayAudit database, while keeping the logon trigger!)

Through the years there have been people who have suggested improvements or provided input to this article or its predecessor. I like to extend big thanks to current and former SQL Server MVPs Dan Guzman, Martin Bell, Adam Machanic, Hugo Kornelis, Razvan Socol, Kent Tegels and Victor Isakov as well as to Imran Mohamed, Jerry Horochowianka, Andrew Chester and Mark Belding.

If you have suggestions for improvements, corrections on contents, language or formatting, please mail me at esquel@sommarskog.se. If you have technical questions that any knowledgeable person could answer, I encourage you to post a question to the SQL Server Security forum on MSDN/Technet.

...and beware of TRUSTWORTHY!

Revision History

2018-01-01
Some minor modifications to sections Multiple Lines of Defence and Impersonating Users as suggested by SQL Server MVP Dan Guzman.
2017-12-03
I have rewritten the entire article, or rather, I have written a new one. The article is now in two parts, one main article and one appendix with examples to solve specific problems. I had intended the main article to be shorter than the previous version, but it proved to be quite longer. Not because I have added a lot of material, but because I present things in a slower pace and rather than showing long scripts with all at once, I now show pieces snippet by snippet. The article is accompanied by a couple of scripts to make it easier to run the demos.

Since the article is entirely rewritten, I have truncated much of the revision history, only retaining the first entry.

2006-02-19
Original version of the original article.

Back to my home page.