Packaging Permissions in Stored Procedures

Appendix of Examples

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

Introduction

This article is an appendix to the article Packaging Permissions in Stored Procedures. Each chapter in this appendix looks at a specific scenario, and how the techniques I present in the main article can be used in that scenario. The chapters are (with one exception) independent of each other, so that you only need to read the chapters of interest to you. (This means that if you read the full appendix, you will have to accept that I repeat myself in places.) Depending on what chapter(s) you choose to read, you may not have to have read the main article in full. The introduction of each chapter specifies what parts of the main article that is the prerequisite for that chapter.

Just like in the main article, there is an accompanying script for each chapter which includes the snippets in the text, with each snippet separated by a line of hyphens. Some scripts have lines of equal signs in places where you need to change your connection. Each script ends with a clean-up section that drops all objects created in the script. With one exception, the scripts are independent of each other, and there is no dependency on the scripts for the main article. We will however make use of the utility stored procedure GrantPermsToSP and the utility script GrantPermsToSP_server.sql. All scripts are developed and tested on SQL 2012 and SQL 2017, unless otherwise noted. To get all scripts locally, download this zip archive.

All scripts create at least one database. Just like in the main article, all databases have the string Play in the name. To adhere with the practice established in the section The Dangers of TRUSTWORTHY, all databases are created to be owned by a dedicated unprivileged SQL login with a throw-away password, and which is disabled from logging in. I mention the principle here as well, since this section is not a prerequisite for all chapters. ...and for true Stones fans, I'm sorry, but the theme does not change.

These are the scenarios I look at:

1. Using DBCC CHECKIDENT in application code.
2. Packaging truncation of a table and reload with BULK INSERT.
3. Permitting activation procedures for Service Broker do things outside the database.
4. Performing server-level actions from contained databases.
5. Letting users start a specific job.
6. Controlling access to linked servers.
7. Loading assemblies directly from source code using the white-listing functionality introduced in SQL 2017.

Using DBCC CHECKIDENT

In this chapter, we will look at how we can package DBCC CHECKIDENT in a stored procedure with help of certificate signing, both by granting the certificate user membership in a role and by granting a permission.

The prerequisite for this chapter is the first four chapters in the main article (save for the last section of chapter four).

The script for this chapter is found in app_checkident.sql.

The Problem

In the system I normally work with, we had a situation where an IDENTITY column defined as int overflowed the maximum value, which caused a full stop. Thankfully, the urgent situation for this table could easily be addressed by running DBCC CHECKIDENT with the RESEED option to a low number, as rows in this table are always aged out after some time.

To avoid that this would happen again, a colleague looked into changing the data type to bigint. He asked how costly such a change would be. Changing the data type of a primary-key column is never for free, because you have to drop the PK constraint and recreate it after the change. If there are referencing foreign keys, these tables need to be changed too. Changing the data type of a column from one fixed-size type to another, requires that the entire table to be reloaded one way or another, be that by ALTER TABLE ALTER COLUMN or a manually crafted script. While we have tools to help us with the script generation, the execution time in production is still a hurdle for a table that is several gigabytes in size.

My colleague therefore suggested that one of the stored procedures that maintains the table should check whether the IDENTITY column has exceeded a certain limit and in this case use DBCC CHECKIDENT to reset the maximum value of the IDENTITY column to 1. This is a viable solution, given the strict purging scheme for this table. (That is, there is no risk that there are low id values around.)

There is a small problem, though, with putting DBCC CHECKIDENT in a stored procedure which is to be executed by a plain user. (The procedure is executed as part of the nightly batch, which is executed through a job scheduler which runs with regular user permissions, which in our case is EXECUTE and SELECT permissions on database level.) Books Online says this:

Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Certificate signing to the rescue!

Setup for This Chapter

Let's first start with a setup similar to the one we used in the main article. Our test user this time is Julia, and since we are working on database level, she does not need a login.

SET XACT_ABORT, NOCOUNT ON
USE master
go
CREATE DATABASE PlayIdent
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayIdent$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayIdent$owner DISABLE
DENY CONNECT SQL TO PlayIdent$owner
ALTER AUTHORIZATION ON DATABASE::PlayIdent TO PlayIdent$owner
go
USE PlayIdent
go
CREATE ROLE Playrole
GRANT EXECUTE ON SCHEMA::dbo TO Playrole
CREATE USER Julia WITHOUT LOGIN
ALTER ROLE Playrole ADD MEMBER Julia

We have a table with an IDENTITY column and we fill it with some data:

CREATE TABLE IdentTable(id        int           NOT NULL IDENTITY,
                        somedata  nvarchar(128) NOT NULL,
                        whodidit  sysname       NOT NULL 
                           CONSTRAINT default_IdentTable_whodidit DEFAULT USER,
                        CONSTRAINT pk_IdentTable PRIMARY KEY(id))
go
INSERT IdentTable(somedata) 
   SELECT name FROM sys.columns

We also have a stored procedure, which is fairly non-sensical. It permits the user to pass an id to set the current value, and then insert a row, of which the id will actually be @id + 1. But it is enough to demonstrate permissions.

CREATE PROCEDURE add_identdata @id      int,
                               @somedata nvarchar(128) AS
   DECLARE @curmax int
   SELECT @curmax = MAX(id) FROM IdentTable
   IF @id <= @curmax
   BEGIN
      RAISERROR('Cannot insert @id %d, as current max is %d', 16, 1, @id, @curmax)
      RETURN 1
   END
   DBCC CHECKIDENT('IdentTable', RESEED, @id) -- WITH NO_INFOMSGS
   INSERT IdentTable(somedata) VALUES (@somedata)
   SELECT id, somedata, whodidit FROM IdentTable WHERE id = scope_identity()

Let's look at what happens when Julia tries this directly.

EXECUTE AS USER = 'Julia'
EXEC add_identdata 9000, 'Half of what I say is meaningless'
go
REVERT

This is the error message:

The Solution

Let's now sign the procedure. In the main article we learnt to use the procedure GrantPermsToSP. Let's make use of this convenient procedure which you find in GrantPermsToSP.sql. Be careful to change the first line to read:

USE PlayIdent

Before we go on, let's ponder what is the proper permission to specify. According the excerpt from Books Online, there is no permission that is applicable here, only ownership or role membership. Since GrantPermsToSP supports role membership, this is not an obstacle. Two roles were listed, and in accordance with "never grant more permissions than needed", we choose the least elevated role of the two, db_ddladmin. Thus:

DECLARE @perms Management.Permission_list
INSERT @perms VALUES ('db_ddladmin')
EXEC Management.GrantPermsToSP 'add_identdata', @perms, @debug = 1

The output is:

CREATE CERTIFICATE [add_identdata$cert]
       ENCRYPTION BY PASSWORD = 'B6166249-10F1-4A4E-AA8D-06ACC5918D37a'
       WITH SUBJECT = '"GRANT db_ddladmin"'
ADD SIGNATURE TO [dbo].[add_identdata]
       BY CERTIFICATE [add_identdata$cert]
       WITH PASSWORD = 'B6166249-10F1-4A4E-AA8D-06ACC5918D37a'
CREATE USER [add_identdata$certuser] 
                  FROM CERTIFICATE [add_identdata$cert]
ALTER ROLE [db_ddladmin] ADD MEMBER [add_identdata$certuser]

You can see that the last statement is ALTER ROLE, rather than GRANT. Julia gives it a new try:

EXECUTE AS USER = 'Julia'
EXEC add_identdata 9000, 'Half of what I say is meaningless'
go
REVERT

The output:

(Note that the number 697 may vary, depending on your version of SQL Server, as it reflects the number of rows in sys.columns for an empty database.)

As it happens, in our shop we have a different solution to GrantPermsToSP in place for specifying the permissions to be packaged with a stored procedure, and this solution does not have support for role membership, but it only supports GRANT. This seemed like a problem, until we realised that there is an alternate solution, working from the first part in BOL: the ownership of the table. What we can do is EXECUTE AS USER = 'dbo' in the procedure, so that DBCC CHECKIDENT is executed by dbo, and then package the permission to impersonate dbo with the procedure.

Here is the changed procedure:

ALTER PROCEDURE add_identdata @id      int,
                               @somedata nvarchar(128) AS
   DECLARE @curmax int
   SELECT @curmax = MAX(id) FROM IdentTable
   IF @id <= @curmax
   BEGIN
      RAISERROR('Cannot insert @id %d, as current max is %d', 16, 1, @id, @curmax)
      RETURN 1
   END
   EXECUTE AS USER = 'dbo'
      DBCC CHECKIDENT('IdentTable', RESEED, @id) WITH NO_INFOMSGS
   REVERT
   INSERT IdentTable(somedata) VALUES (@somedata)
   SELECT id, somedata, whodidit FROM IdentTable WHERE id = scope_identity()

Note that beside adding the impersonation of dbo, we have also added WITH NO_INFOMSGS, so that we don't see the informational message about the IDENTITY value being changed.

We grant permissions:

DECLARE @perms Management.Permission_list
INSERT @perms VALUES ('IMPERSONATE ON USER::dbo')
EXEC Management.GrantPermsToSP 'add_identdata', @perms, @debug = 1

The output:

DROP USER [add_identdata$certuser]
DROP CERTIFICATE [add_identdata$cert]
CREATE CERTIFICATE [add_identdata$cert]
       ENCRYPTION BY PASSWORD = '4C22B9C1-0560-45FF-9DFE-20B70C0A04BEa'
       WITH SUBJECT = '"GRANT IMPERSONATE ON USER::dbo"'
ADD SIGNATURE TO [dbo].[add_identdata]
       BY CERTIFICATE [add_identdata$cert]
       WITH PASSWORD = '4C22B9C1-0560-45FF-9DFE-20B70C0A04BEa'
CREATE USER [add_identdata$certuser] 
                  FROM CERTIFICATE [add_identdata$cert]
GRANT IMPERSONATE ON USER::dbo TO [add_identdata$certuser]

Julia tests that this works:

EXECUTE AS USER = 'Julia'
EXEC add_identdata 9100, 'All My Loving'
go
REVERT

And indeed:

The advantage with using certificate signing to permit an EXECUTE AS statement in the procedure body, over using the EXECUTE AS clause in the procedure header, is that makes it easier to apply the impersonation only in the spot where it is needed for permissions.

In this chapter you have seen how you can use certificate signing to package DBCC CHECKIDENT inside a stored procedure. You have also seen that if the permission you need to package is related to ownership, you can use the EXECUTE AS statement, and package IMPERSONATE permission with the stored procedure.

Here is the clean-up for this chapter:

USE tempdb
go
ALTER DATABASE PlayIdent SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PlayIdent
go
DROP LOGIN PlayIdent$owner

Using BULK INSERT to reload a table

In this chapter you will learn how you can package the permissions to run BULK INSERT in a stored procedure, which requires a little extra quirk. This example also shows how a procedure can be signed with multiple certificates to add permissions.

To fully grasp this chapter, you should have read the first five chapters of the main article (save for the last section of chapters four).

The script for this chapter is app_bulkinsert.sql.

The Setup

Before we can start, we need a database to play with. We need a test user, and since we will work with server-level permissions, this will have to be a test user with a login. A certain Eleanor Rigby has volunteered for the task.

SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE DATABASE BulkPlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN BulkPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN BulkPlay$owner DISABLE
DENY CONNECT SQL TO BulkPlay$owner
ALTER AUTHORIZATION ON DATABASE::BulkPlay TO BulkPlay$owner
go
CREATE LOGIN ERigby WITH PASSWORD = 'Look at the all the lonely people'

We also need a test file to bulk load. To keep everything in the script, I use xp_cmdshell to create it, but if you don't like enabling xp_cmdshell, you can create the file in a different way. Just make sure that there are no blanks after the last semicolon on each line. Also, if you don't have a C:\temp, feel free to change the path. (For reasons entirely unknown to me, Windows machines do not come with a C:\temp by default, but who does not create one?)

EXEC xp_cmdshell 'ECHO 1;The Fool on the Hill;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 2;The Ballad of John and Yoko;>> C:\temp\bulktest.txt'

We move over to the database where we create our standard arrangements for user and role, and we also create a test table which we add a row to.

USE BulkPlay
go
CREATE ROLE BulkRole
CREATE USER ERigby
ALTER ROLE BulkRole ADD MEMBER ERigby
GRANT EXECUTE ON SCHEMA::dbo TO BulkRole
go
CREATE TABLE Bulktable(id       int          NOT NULL,
                       somedata varchar(40)  NOT NULL,
                       loadtime datetime2(3) NOT NULL
                          CONSTRAINT def_Bulktable_loadtime DEFAULT sysdatetime(),
                       CONSTRAINT pk_Bulktable PRIMARY KEY (id)
)
INSERT Bulktable (id, somedata) VALUES (1, 'Rubber Soul')

Then we need a stored procedure to play with. The task of the stored procedure is to truncate the table and reload it from the fixed path C:\temp\bulktest.txt. To verify that the reload ran well, we return all data in the table in a SELECT. For diagnostic purposes, we also inspect the contents in sys.login_token and sys.user_token. If you don't have a C:\temp and changed the path above, be careful to change the path in the procedure as well.

CREATE PROCEDURE reload_table AS
   SELECT name, usage, type FROM sys.login_token
   SELECT name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   BULK INSERT Bulktable FROM 'C:\temp\bulktest.txt'
   WITH (DATAFILETYPE = 'char',
         FIELDTERMINATOR = ';')
   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id

Problem and Solution

We learnt already in the main article that TRUNCATE TABLE requires ALTER permission on the table, and this is nothing you get through ownership chaining. Thus, we must package that permission with the procedure. To this end we use GrantPermsToSP which we learnt about in the main article. Open GrantPermsToSP.sql in a separate query window, and change the first line to read

USE BulkPlay

Run the file to create GrantPermsToSP in BulkPlay. Once this is done, we can sign the procedure:

DECLARE @perms Management.Permission_list
INSERT @perms VALUES('ALTER ON Bulktable')
EXEC Management.GrantPermsToSP 'reload_table', @perms, @debug = 1

The output is:

CREATE CERTIFICATE [reload_table$cert]
       ENCRYPTION BY PASSWORD = 'A378B621-D094-41EE-872E-BB906CB8FEE4a'
       WITH SUBJECT = '"GRANT ALTER ON Bulktable"'
ADD SIGNATURE TO [dbo].[reload_table]
       BY CERTIFICATE [reload_table$cert]
       WITH PASSWORD = 'A378B621-D094-41EE-872E-BB906CB8FEE4a'
CREATE USER [reload_table$certuser] 
                  FROM CERTIFICATE [reload_table$cert]
GRANT ALTER ON Bulktable TO [reload_table$certuser]

Before we go on, we let our volunteer make a first attempt to run the procedure:

EXECUTE AS LOGIN = 'ERigby'
EXEC reload_table
go
REVERT

This results in an error message:

Books Online informs us that to use BULK INSERT, you need the server-level permission ADMINISTER BULK OPERATIONS. This can also be achieved with membership in the fixed server role bulkadmin. Because the latter is shorter to type, we use that.

In chapter five of the main article, we learnt about a script we can use to automate the assignment of server-level permissions. Open GrantPermsToSP_server.sql in SSMS and change the parameter part in the header to read:

DECLARE @database nvarchar(260) = 'BulkPlay',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES ('bulkadmin')

(This also appears as a comment in the main script for the chapter to remind you.)

This script produces a considerable amount of output, and here I leave out the initial parts that drops any existing certificate etc, and include only the part that creates the new certificate, signs the procedure, drops the private key, copies the certificate to master, creates the login and adds the login to the bulkadmin role. Some lines have been abbreviated and some lines have been broken into several to fit the article format:

-- In database [BulkPlay]
EXECUTE AS USER = 'dbo'
CREATE CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"
       ENCRYPTION BY PASSWORD = '03BCADDA-3208-422E-9FFB-3F04F87636BBAa0'
       WITH SUBJECT = '"GRANT bulkadmin"'
REVERT

-- In database [BulkPlay]
EXECUTE AS USER = 'dbo'
ADD SIGNATURE TO [dbo].[reload_table] 
         BY CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"
         WITH PASSWORD = '03BCADDA-3208-422E-9FFB-3F04F87636BBAa0'
REVERT

-- In database [BulkPlay]
EXECUTE AS USER = 'dbo'
ALTER CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]" REMOVE PRIVATE KEY
REVERT

-- In database [BulkPlay]
EXECUTE AS USER = 'dbo'
SELECT @pvt = pvt_key_encryption_type FROM sys.certificates WHERE name = @certname
REVERT

-- In database [BulkPlay]
EXECUTE AS USER = 'dbo'
SELECT @public_key  = convert(varchar(MAX),
                            certencoded(cert_id(quotename(@certname))), 1)
REVERT

-- In master
CREATE CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"
       FROM BINARY = 0x308202BC308201A4A00302010202...

-- In master
CREATE LOGIN "SIGN [BulkPlay].[dbo].[reload_table]" 
       FROM CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"

-- In master
ALTER SERVER ROLE [bulkadmin] ADD MEMBER "SIGN [BulkPlay].[dbo].[reload_table]"

Thinking that everything is set up for her, Eleanor Rigby tries the procedure again:

EXECUTE AS LOGIN = 'ERigby'
EXEC reload_table
go
REVERT

However, the error message is exactly the same.

You see, BULK INSERT is a special case. You may or may not have paid attention to it, but there is hint already in the output from the Ms. Rigby's first attempt. When you think of it: if the error message would be coming from an attempt to execute the BULK INSERT statement, wouldn't we expect to see the output from sys.login_token and sys.user_token? Furthermore, if you look in Bulktable:

SELECT * FROM Bulktable

You find that the row we inserted initially is still there. That is, the table has not been truncated.

Thus, the conclusion has to be that the procedure never started, but the error occurred already when the plan for the procedure was compiled. It is not entirely logical, but SQL Server actually accesses the file already at compilation, and for this reason you need ADMINISTER BULK OPERATIONS or membership in bulkadmin even to compile and create a plan for the procedure.

The easiest way to work around this problem is to move the BULK INSERT statement to an inner scope of dynamic SQL, since the powers of the certificate applies inside the dynamic SQL as well. (Again, don't forget to change the path if you put the data file elsewhere.)

ALTER PROCEDURE reload_table AS
   SELECT name, usage, type FROM sys.login_token
   SELECT name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   EXEC ('BULK INSERT Bulktable FROM ''C:\temp\bulktest.txt''
          WITH (DATAFILETYPE = ''char'',
                FIELDTERMINATOR = '';'')')
   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id

Note: in many cases when you use BULK INSERT, the file name is not known until run-time, in which case you need to use dynamic SQL anyway, since BULK INSERT does not accept a parameter for the file name.

Since the procedure was changed, the signatures were lost and we need to re-sign it. When adding the database permissions, we need to keep in mind that since BULK INSERT is now in dynamic SQL, we also need to package INSERT permission with the procedure, since ownership chaining no longer applies. (To be perfectly honest: I do not know whether ownership chaining applies to BULK INSERT in the first place.) Thus:

DECLARE @perms Management.Permission_list
INSERT @perms VALUES('ALTER ON Bulktable'),
                    ('INSERT ON Bulktable')
EXEC Management.GrantPermsToSP 'reload_table', @perms, @debug = 1

For the server-level permission, we can use the script GrantPermsToSP_server.sql as we set it up initially:

DECLARE @database nvarchar(260) = 'BulkPlay',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES ('bulkadmin')

Eleanor Rigby now makes her third attempt:

EXECUTE AS LOGIN = 'ERigby'
EXEC reload_table
go
REVERT

This time she is successful:

We can take the opportunity to look at the output from the two views. In sys.login_token, we first see ERigby herself and public that everyone is a member of. Next we see bulkadmin, despite that Eleanor is not a member of bulkadmin. Instead this token comes from the certificate login SIGN [BulkPlay].[dbo].[reload_table]. We also see both certificates listed on their own. As I said in the main article, I don't know why, and I don't think it matters.

In sys.user_token, we see ERigby together with the roles public and BulkRole she is a member of. Here we also see the user for the database certificate, that is, the user that contributes with the permission to insert into and truncate Bulktable.

You may ask, why I bother with two certificates. Couldn't it be arranged with a single certificate? Indeed, this is perfectly possible. You could create a user from SIGN [BulkPlay].[dbo].[reload_table] in BulkPlay and grant this user INSERT and ALTER on Bulktable. However, there are several reasons why I chose to use two certificates:

1. I wanted to show an example with two certificates, would you be in doubt that it works.
2. We have ready-made automation for database-only certificates and server-only certificates, so it easier to use separate certificates.
3. Last and most important: separation of duties. The server-level DBA grants bulk-load permission after having reviewed the code to see that the file is loaded from the agreed location, whereas the local power user (like Sgt Pepper in Playground in the main article) handles the database-level permissions as he or she sees fit without involving the DBA.

Here is the clean-up script for this chapter. However, if you plan to work with the next chapter on Service Broker, do not run the clean-up, as that chapter use the same setup and continues where this one ends.

USE master
go
ALTER DATABASE BulkPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE BulkPlay
go
DROP LOGIN ERigby
go
EXEC xp_cmdshell 'DEL C:\temp\bulktest.txt'
go
DROP LOGIN "SIGN [BulkPlay].[dbo].[reload_table]"
go
DROP LOGIN BulkPlay$owner
go
DROP CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"

Activation Procedures for Service Broker

In this chapter we will look at how to perform server-level tasks and cross-database access from activation procedures for Service Broker.

To have full understanding of this chapter, I recommend that you have read up to section 8.2 (The Dangers of TRUSTWORTHY) in the main article. You also need to have read the previous chapter on bulk load in this appendix, as we will continue to work with that example. I will assume that you have a working knowledge of Service Broker.

The script for this chapter is in app_servicebroker.sql. Note that this script assumes that you have first executed app_bulkinsert.sql (except for the clean-up at the end).

The Setup

In this chapter we will run reload_table from an activation procedure. As it is somewhat unpractical to have result sets in Service Broker activation procedures, we will instead write the output from the token views to a debug table:

SET NOCOUNT, XACT_ABORT ON
USE BulkPlay
go
CREATE TABLE debug_table (data    char(2)       NOT NULL,
                          token   nvarchar(128) NOT NULL,
                          usage   nvarchar(40)  NULL,
                          type    nvarchar(40)  NULL
)
go
ALTER PROCEDURE reload_table AS
   INSERT debug_table (data, token, usage, type)
      SELECT 'LT', name, usage, type FROM sys.login_token
   INSERT debug_table (data, token, usage, type)
      SELECT 'UT', name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   EXEC ('BULK INSERT Bulktable FROM ''C:\temp\bulktest.txt''
          WITH (DATAFILETYPE = ''char'',
                FIELDTERMINATOR = '';'')')

We need to re-sign it, first on database level:

DECLARE @perms Management.Permission_list
INSERT @perms VALUES('ALTER ON Bulktable'),
                    ('INSERT ON Bulktable')
EXEC Management.GrantPermsToSP 'reload_table', @perms, @debug = 1

And then on server level, from the script GrantPermsToSP_server.sql (which you should keep open; we will use it more in this chapter), set up in the same way as before:

DECLARE @database nvarchar(260) = 'BulkPlay',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES ('bulkadmin')

We now turn to the Service Broker configuration. I like to stress that what follows is in no way a pattern for how to write Service Broker applications, but I'm committing all sorts of sins in order to keep the code short and concise to focus on the problem at hand. We begin with creating a message type, a contract, a sender and a receiver queue and the corresponding services. The receiver queue is configured to have activation, and in order to do that, we create the activation procedure as a stub.

CREATE MESSAGE TYPE FileImport VALIDATION = NONE
CREATE CONTRACT FileImportContract (FileImport SENT BY INITIATOR)  
go
CREATE PROCEDURE FileImportFromQueue AS PRINT 1
go
CREATE QUEUE ReceiverQueue
WITH   STATUS = ON, 
       ACTIVATION (STATUS = ON, 
                   PROCEDURE_NAME = dbo.FileImportFromQueue, 
                   MAX_QUEUE_READERS = 1, 
                   EXECUTE AS OWNER)
go
CREATE QUEUE SenderQueue WITH STATUS = ON
go
CREATE SERVICE ReceiverService ON QUEUE dbo.ReceiverQueue (FileImportContract)
CREATE SERVICE SenderService ON QUEUE dbo.SenderQueue (FileImportContract)

You may note that activation is set up with EXECUTE AS OWNER. I don't think this is the best of ideas, but we keep it this way for now.

We can now define our activation procedure for real. Since we can expect errors in our tests, we call reload_table in TRY-CATCH, and insert any errors into the debug table.

ALTER PROCEDURE FileImportFromQueue AS
SET XACT_ABORT, NOCOUNT ON
   DECLARE  @dialog  uniqueidentifier,
            @msgtype sysname

   ;RECEIVE TOP(1) @dialog  = conversation_handle,
                   @msgtype = message_type_name
   FROM ReceiverQueue

   IF @msgtype = 'FileImport'
   BEGIN
      BEGIN TRY
         EXEC reload_table
      END TRY
      BEGIN CATCH
         INSERT debug_table(data, token)
            VALUES ('E', substring(error_message(), 1, 128))
      END CATCH
   END
   
   END CONVERSATION @dialog

The final step in the setup is a sender procedure that starts a conversation and sends a message to the ReceiverService. It then waits for four seconds for the EndDialog message to come back, before giving up and no matter what it closes the conversation on its side. Finally, it displays the contents in debug_table and Bulktable.

CREATE PROCEDURE FileImportInitiate AS
   SET XACT_ABORT, NOCOUNT ON

   DECLARE @dialog   uniqueidentifier,
           @msgbody  xml

   DELETE debug_table

   BEGIN DIALOG @dialog
   FROM SERVICE SenderService
   TO SERVICE   'ReceiverService', 'CURRENT DATABASE' 
   ON CONTRACT  FileImportContract
   WITH ENCRYPTION = OFF, LIFETIME = 3600 

   ;SEND ON CONVERSATION @dialog MESSAGE TYPE FileImport (0x) 

   WAITFOR (RECEIVE TOP (1) @msgbody = message_body
            FROM    SenderQueue), TIMEOUT 4000

   END CONVERSATION @dialog

   --SELECT CAST(@msgbody AS xml) AS response
   SELECT data, token, usage, type
   FROM   debug_table

   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id

The point with the commented SELECT of @msgbody is that if the procedure comes back immediately with no data in debug_table, there was probably an error response of which we can find the text in @msgbody. (If there is a delay of four seconds before the procedure comes back empty-handed, a good place for finding the error message is sys.transmission_queue.)

We also take the occasion to replace the data file, so that we can see whether we succeeded or just left the old data around:

EXEC xp_cmdshell 'ECHO 11;She''s Leaving Home;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 12;Meeting a man from the motor trade;>> C:\temp\bulktest.txt'

Problem and Solution

We are now ready to do some testing, and just for sports, we let Eleanor Rigby run FileImportInitiate

EXECUTE AS LOGIN = 'ERigby'
EXEC FileImportInitiate
go
REVERT

This is what she sees:

As a first observation, we can note that Eleanor Rigby was able to start the dialog, send the message and receive the response. This means that ownership chaining applies to these commands, which is practical.

Now over to the main problem. Since Bulktable is empty we can understand that the TRUNCATE TABLE operation succeeded, but the BULK INSERT operation failed. The error message tells us the direct reason why it failed: there is no permission to run BULK INSERT. This is certainly perplexing. The procedure is signed with a certificate. Why does this fail?

If we look at the output from sys.login_token we can go back one step in the chain of reasons. The usage column reads DENY ONLY for all tokens. How come? Let's first understand who the current user and login are from the output above. There is a row where the type column reads SQL USER, and the token on this line is dbo, as we can expect when activation is set to run with EXECUTE AS OWNER. Likewise, there is one line with type = SQL LOGIN and the token here is BulkPlay$owner, the owner of the BulkPlay database so that matches dbo.

But why isn't BulkPlay$owner or the certificate trusted? We saw this already in chapter six in the main article. We had an application login that used the EXECUTE AS USER statement to impersonate the actual user. We found that in this case, we could not run the procedure ShowSessions successfully despite that it was signed with a certificate. And this is the same story. That is, the EXECUTE AS clause for queue activation is the same as the EXECUTE AS USER statement. The activation clause does not say EXECUTE AS USER explicitly, but USER is implied. Thus, we have now reached the technical start of the reason chain: we are impersonating a database user, and in this case it does not help if the server-level permission to perform BULK INSERT present, because the user itself is not trusted on server level.

And we learnt in the section The Dangers of TRUSTWORTHY the rationale for this rule. If this restriction wasn't in place, a power user in the BulkPlay database could create an activation procedure which includes all sort of actions on server level, and configure activation with

EXECUTE AS 'LIVERPOOL\GMartin'

The activation procedure would then be running with sysadmin permission. (You may recall from the main article that GMartin is the server-level DBA in the LIVERPOOL domain.). So in order to prevent permission elevation, whenever a database user is impersonated through the EXECUTE AS USER statement, the context is not trusted but sandboxed into the current database. That is, the situation is the same as when we tried to use the EXECUTE AS clause to package server-level permissions in a stored procedure. We learnt that to get out of the sandbox to perform actions on server level or in another database, two doors need to be opened:

1. The database must be TRUSTWORTHY to get out.
2. The database owner must have AUTHENTICATE permission where we want to go.

We also we learnt that this opens for exactly the permission elevation that the sandbox is there to prevent and concluded that this is rarely an acceptable solution. But, at least to verify that we are on the right track, we can give it a quick shot. That is, we make the database trustworthy, grant AUTHENTICATE SERVER to BulkPlay$owner, run the procedure and directly revert our actions:

USE master
go
ALTER DATABASE BulkPlay SET TRUSTWORTHY ON
GRANT AUTHENTICATE SERVER TO BulkPlay$owner
go
EXEC BulkPlay..FileImportInitiate
go
ALTER DATABASE BulkPlay SET TRUSTWORTHY OFF
REVOKE AUTHENTICATE SERVER TO BulkPlay$owner

Indeed, this worked out:

All login tokens now have GRANT OR DENY, and the activation procedure can now run the BULK INSERT statement. BulkPlay$owner now appears both as SQL LOGIN and AUTHENTICATOR. That is, it has been trusted to vouch for the users in the database (including itself).

When we looked at this situation for EXECUTE AS in a stored procedure, we stopped there, because we already had a working solution with certificate signing when the sole task was to package server-level permission in a stored procedure. But now we need to try harder, because we want to use Service Broker activation without having to grant AUTHENTICATE SERVER.

But what says that this permission must be granted to the database owner? What if we grant it to the certificate? Let's try that.

USE master 
go
GRANT AUTHENTICATE SERVER TO "SIGN [BulkPlay].[dbo].[reload_table]"
go
EXEC BulkPlay..FileImportInitiate
go
REVOKE AUTHENTICATE SERVER TO "SIGN [BulkPlay].[dbo].[reload_table]"

No, that did not work out. The output is the same as in our first attempt. The impersonated context is not trusted. And if you give it a thought you can see a good reason for this. Say that this would actually work. How would the server-level DBA know which user that is being impersonated and to which he gives server-level access by signing the procedure? The answer is that he would not. Any power user could say:

EXECUTE AS USER = 'LIVERPOOL\GMartin'
EXEC reload_table

For the actual case of reload_table this is a not an issue, because it is very confined in what it does. Specifically, it does not call any other stored procedures. But say that it had – the contents of that inner procedure would run as GMartin and the full powers of sysadmin because once you have been authenticated somewhere, you remain authenticated. It's not like regular certificate signing where the powers of the signature are removed when you enter an inner module. This inner procedure would not be in the control of the server-level DBA, as local power users can change it without invalidating the signature on reload_table. There is more to watch for than stored procedures. Assume that the TRUNCATE TABLE statement had been a DELETE statement instead. A malicious power user could later add a DELETE trigger on Bulktable which would execute as GMartin.

For this reason, Microsoft has designed it so that the AUTHENTICATE SERVER permission through a certificate signature only takes effect where the name of the impersonated user is included in the signature. This permits the DBA to verify that the user impersonated is acceptable to use. With the current setup, impersonation happens on the queue activation. We cannot sign the queue activation, but we can repeat the impersonation with an EXECUTE AS clause in a stored procedure and then sign that procedure. One place to do this is the activation procedure. Thus:

USE BulkPlay
go
ALTER PROCEDURE FileImportFromQueue WITH EXECUTE AS OWNER AS
SET XACT_ABORT, NOCOUNT ON
   DECLARE  @dialog  uniqueidentifier,
            @msgtype sysname

   ;RECEIVE TOP(1) @dialog  = conversation_handle,
                   @msgtype = message_type_name
   FROM ReceiverQueue

   IF @msgtype = 'FileImport'
   BEGIN
      BEGIN TRY
         EXEC reload_table
      END TRY
      BEGIN CATCH
         INSERT debug_table(data, token)
            VALUES ('E', substring(error_message(), 1, 128))
      END CATCH
   END
   
   END CONVERSATION @dialog

And then we use the script GrantPermsToSP_server.sql to sign the procedure and bestow it with AUTHENTICATE SERVER.

  DECLARE @database nvarchar(260) = 'BulkPlay',
           @procname nvarchar(520) = 'FileImportFromQueue'

   DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
   INSERT @perms VALUES
      ('AUTHENTICATE SERVER')

We can now try to trigger the activation procedure again:

EXEC FileImportInitiate

Success! This is the output:

Observe the appearance of SIGN [BulkPlay].[dbo].[FileImportFromQueue]. It does not appear with GRANT OR DENY, since that effect was lost when we entered reload_table. However, it still remains as an AUTHENTICATOR so that the execution context is trusted on server level.

Initially, I said that I don't really like using EXECUTE AS OWNER for queue activation. I think it's better to create a login-less user for the task. This agrees with the principle of granting minimum permission and this also permits us to see that updates are coming from Service Broker in our auditing and monitoring. So, let's do this:

CREATE USER sb_user WITHOUT LOGIN
GRANT EXECUTE ON FileImportFromQueue TO sb_user
go
ALTER QUEUE ReceiverQueue
WITH  STATUS = ON, 
      ACTIVATION (STATUS = ON, 
                  PROCEDURE_NAME = dbo.FileImportFromQueue, 
                  MAX_QUEUE_READERS = 1, 
                  EXECUTE AS 'sb_user')

For this to be meaningful, we also need to change the EXECUTE AS clause in FileImportFromQueue accordingly:

ALTER PROCEDURE FileImportFromQueue WITH EXECUTE AS 'sb_user' AS
SET XACT_ABORT, NOCOUNT ON
   DECLARE  @dialog  uniqueidentifier,
            @msgtype sysname

   ;RECEIVE TOP(1) @dialog  = conversation_handle,
                   @msgtype = message_type_name
   FROM ReceiverQueue

   IF @msgtype = 'FileImport'
   BEGIN
      BEGIN TRY
         EXEC reload_table
      END TRY
      BEGIN CATCH
         INSERT debug_table(data, token)
            VALUES ('E', substring(error_message(), 1, 128))
      END CATCH
   END
   
   END CONVERSATION @dialog

Since we changed it, we need to rerun GrantPermsToSP_server.sql to grant AUTHENTICATE SERVER:

DECLARE @database nvarchar(260) = 'BulkPlay',
        @procname nvarchar(520) = 'FileImportFromQueue'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
    ('AUTHENTICATE SERVER')

And to prove the effect, we change the data file:

EXEC xp_cmdshell 'ECHO 21;Ob-la-di Ob-la-da;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 22;Life goes on bra;>> C:\temp\bulktest.txt'

Let's give it a try:

EXEC FileImportInitiate

And indeed:

There are a lot of tokens here, and while have seen variations of this list a few times now, it is worth doing a recap to see what all these tokens are:

• S-1-9-3-3155503315... This is the SID for the login-less user sb_user.
• public. The fixed server role that all logins are member of.
• bulkadmin. The role with the permission ADMINISTER BULK OPERATIONS.
• SIGN [BulkPlay].[dbo].[reload_table]. The certificate login that is a member of bulkadmin and thus brings that token into the mix.
• SIGN [BulkPlay].[dbo].[reload_table]. Appears also a certificate, presumably without any real function.
• S-1-9-1-227419230... This SID represents the database certificate reload_table$cert. It has no function on server-level, but it appears nevertheless.
• SIGN [BulkPlay].[dbo].[FileImportFromQueue]. The certificate serves as an authenticator and gives sb_user the right to operate on server level.
• sb_user. We are now among the database-level tokens and this is the current database user in BulkPlay.
• public. The fixed database role public, that all users are member of.
• reload_table$certuser. The user that contributes with the permission to truncate Bulktable and insert rows to it.

Now we know the technique to solve the original problem, but there are some delicate questions to answer:

• Which procedure should have EXECUTE AS and be signed with AUTHENTICATE SERVER? The activation procedure? reload_table? Something else?
• What user should we have in the EXECUTE AS clause?

The reason that these questions are delicate is that the answers are different depending on which hat you are wearing. When I say that I prefer to use sb_user over AS OWNER, I'm talking as responsible for the database, and it is also from this perspective that I prefer to have EXECUTE AS in the activation procedure. I prefer sb_user from the principle of granting minimum permission. And I prefer to have the EXECUTE AS clause on a procedure which is only executed by Service Broker, which is why I find the activation procedure to be a good pick. I may want to use reload_table from other places in the application, and in that case I don't want EXECUTE AS in that procedure, as it could cause problems with auditing. (There isn't any auditing in this particular case, but see it as a principle.)

However, seen from the perspective of the server DBA, it is a different matter. You need to beware of the risk that the local power users may cheat on you. There are two things you need to consider:

• If you sign a procedure that calls other procedures or can invoke triggers, they can change that module without invalidating the signature.
• They can remap the database user who is being impersonated to a different login which is more powerful.

If the procedure is very confined in its actions like reload_table in its current shape, that is, it does not call other stored procedures nor can it invoke triggers, the database user does not really matter. Thus, you would be more comfortable with signing reload_table table over FileImportFromQueue. However, can still accept to sign FileImportFromQueue, if you know that the database user being used has no server-level permissions – and will not gain them later without your knowing. It is here the second point comes into play. Say that the power user of BulkPlay presents you with

ALTER PROCEDURE FileImportFromQueue WITH EXECUTE AS 'ERigby' AS

At first glance this may seem OK, since Ms. Rigby has no server-level permission at all. But don't do it! Once you leave the room, the malicious power user would run:

ALTER USER ERigby WITH LOGIN = sa

And off he goes to take over the server.

For this reason, the user that appears in the EXECUTE AS clause must be one of:

• OWNER or dbo.
• A user created WITHOUT LOGIN.

For dbo to be OK, the database must be owned by a dedicated SQL login as we established in the main article. (It is of course entirely unacceptable if the database is owned by sa!). And OWNER is good to, as long as the procedure is actually owned by dbo and not any other user. This leaves the server-level DBA in control, since the local power user cannot remap dbo. Nor can they change the owner. Or rather they can change the owner – but this invalidates the signature. Actually, if you look at the output GrantPermsToSP_server.sql produced when you granted AUTHENTICATE SERVER you will find this message:

The module being signed is marked to execute as owner. If the owner changes the signature will not be valid.

A user created WITHOUT LOGIN is also reasonably safe, because you cannot remap such a user to a login. Nor can the local power user drop the login and re-create it with a more powerful login mapping, because you cannot drop a user which appears in an EXECUTE AS clause. Nor does it help to rename the user and try to drop it, because EXECUTE AS is tracked by execute_as_principal_id in sys.sql_modules.

To verify that the user is actually a user created WITHOUT LOGIN, you can do this:

ALTER USER sb_user WITH LOGIN = nosuchlogin

This should return the error message:

If you instead get:

This might mean that the local power user is trying to trick you. In any case, you should not agree to sign the procedure.

Thus, as you have seen, there are possibilities for you to come on terms with the local power user and still be in full control. But if you are a busy DBA who administers umpteen servers, you may still prefer to sign reload_table and see EXECUTE AS 'dbo', as then you do not need to conduct no further checks. Presuming, that is, that you have firmly established the principle "no databases owned by sa" in your shop.

Note: I believe that there is a way to map a login-less user to sa: update the systems tables directly. But this requires that the server is in single-user mode, so the evil power user would first have the DBA to sign the procedure, get a backup of the database, restore it on his own server to hack it, then convince the DBA to restore the backup of the manipulated database. A corollary of this is that when you restore a backup from an untrusted source, you may want to examine all signed modules in that database.

Summary

It has been a long chapter, and I can appreciate if you did not understand exactly all my reasoning of why things work the way they do. In fact, it took me quite some time to understand why Microsoft had made this design, and it was only as I finally sat down and wrote this chapter and developed the accompanying scripts that all pieces fell into place.

Thankfully, if you don't care about why, the chapter can be very simply summarised this way: if you want to perform server-level actions from an activation procedure, you need to take these steps:

1. Repeat the EXECUTE AS clause in the queue activation in the activation procedure, or in the procedure where server-level permissions are needed.
2. Sign the procedure with the EXECUTE AS clause with a certificate, imported from the master database.
3. Grant a login created from that certificate AUTHENTICATE SERVER.
4. To this comes the general steps to package whatever server-level permission you need, for instance ADMINISTER BULK OPERATIONS. You can use the same certificate, if you have the EXECUTE AS clause in the same procedure; else use a different certificate (to tie certificates to the procedure they sign by the name).

Here I have only talked about server-level permission, but if your problem is that you want to perform cross-database access from an activation procedure, and you do not want to make the database trustworthy, the solution is the same. The difference is that the certificate should origin from the target database, and a user created from the certificate should have been granted AUTHENTICATE permission in the that database.

Here is a clean-up script for all that was created in this chapter and the previous:

USE master
go
ALTER DATABASE BulkPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE BulkPlay
go
DROP LOGIN BulkPlay$owner
go
DROP LOGIN ERigby
go
EXEC xp_cmdshell 'DEL C:\temp\bulktest.txt'
go
DROP LOGIN "SIGN [BulkPlay].[dbo].[reload_table]"
go
DROP CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"
go
DROP LOGIN "SIGN [BulkPlay].[dbo].[FileImportFromQueue]"
go
DROP CERTIFICATE "SIGN [BulkPlay].[dbo].[FileImportFromQueue]"

Contained Databases

In a contained database, you can create users that are authenticated directly by the database without involving the server. Contained databases, introduced in SQL 2012, are not widely used, but some sites use it for databases in availability groups, since it saves them from maintaining logins on the individual nodes.

Because the users authenticate directly to the database, they are not recognized on server level, so if you want to package server-level permissions in a stored procedure that is to be used by contained database users, you need to take some extra steps that we will learn in this chapter.

For this chapter, you should have read up to section 8.2 (The Dangers of TRUSTWORTHY) in the main article. If you have read the chapter on bulk load in this appendix, some pieces will appear familiar, as I use BULK INSERT for the example. Those who have read the chapter on Service Broker, will recognize that the underlying problem is the same, although I arrive at a slightly different solution.

The script for this chapter is in app_containeddb.sql.

The Setup

The first step in the setup is to configure the server to permit users authenticated on database level:

SET NOCOUNT, XACT_ABORT ON
USE master
EXEC sp_configure 'contained database authentication', 1
RECONFIGURE

Next step is to create a database that we mark as partially contained:

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

Since we will work with an example with BULK INSERT, we create a data file:

EXEC xp_cmdshell 'ECHO 1;Please, Please Me;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 2;Old Brown Shoe;>> C:\temp\bulktest.txt'

If you prefer, you can create this file without xp_cmdshell. Just make sure that there is no space after the semicolons. If you prefer to change the path (for instance, because you don't have a C:\temp), do so. You will need to remember to replace the occurrences of C:\temp in the snippets that follow as well.

Now we move over to our database and create a contained user for Father McKenzie who we grant permission to run stored procedures through a role. We also create a table to work with and we add an initial row to it.

USE PlayContainer
go
CREATE ROLE ContainerRole
CREATE USER McKenzie WITH PASSWORD = 'No one was saved'
ALTER ROLE ContainerRole ADD MEMBER McKenzie
GRANT EXECUTE ON SCHEMA::dbo TO ContainerRole
go
CREATE TABLE Bulktable(id       int          NOT NULL,
                       somedata varchar(40)  NOT NULL,
                       loadtime datetime2(3) NOT NULL
                          CONSTRAINT def_Bulktable_loadtime DEFAULT sysdatetime(),
                       CONSTRAINT pk_Bulktable PRIMARY KEY (id)
)
INSERT Bulktable(id, somedata) VALUES (1, 'Yesterday')

Next step is to create a test procedure that will truncate Bulktable and then reload it from bulktest.txt. (If you did not put the test file in C:\temp, be careful to change the path here as well.). As for why the BULK INSERT statement is in dynamic SQL, please see chapter on bulk load in this appendix.

CREATE PROCEDURE reload_table AS
   SELECT name, usage, type FROM sys.login_token
   SELECT name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   EXEC ('BULK INSERT Bulktable FROM ''C:\temp\bulktest.txt''
          WITH (DATAFILETYPE = ''char'',
                FIELDTERMINATOR = '';'')')
   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id

The procedure starts off with displaying the information in sys.login_token and sys.user_token for diagnostic purposes. To permit unprivileged users to run the procedure, we need to package database as well as server-level permissions with the procedure through certificate signing. Let us start with the database permissions. In order to truncate the table, ALTER permission on the table must be present, and since BULK INSERT is in dynamic SQL, there is no ownership chaining, so we need to bundle INSERT permission as well. Open the file GrantPermsToSP.sql and create this procedure in the PlayContainer database. (Observe that the first line in this file reads USE Playground, so you need to change this.) Once you have created the procedure, run this:

DECLARE @perms Management.Permission_list
INSERT @perms VALUES('ALTER ON Bulktable'),
                    ('INSERT ON Bulktable')
EXEC Management.GrantPermsToSP 'reload_table', @perms, @debug = 1

The output (as a reminder of what actually happens):

CREATE CERTIFICATE [reload_table$cert]
       ENCRYPTION BY PASSWORD = 'EE30C5D0-A900-4B32-8AD6-A58BC6C75DB2a'
       WITH SUBJECT = '"GRANT ALTER ON Bulktable - INSERT ON Bulktable"'
ADD SIGNATURE TO [dbo].[reload_table]
       BY CERTIFICATE [reload_table$cert]
       WITH PASSWORD = 'EE30C5D0-A900-4B32-8AD6-A58BC6C75DB2a'
CREATE USER [reload_table$certuser] 
                  FROM CERTIFICATE [reload_table$cert]
GRANT ALTER ON Bulktable TO [reload_table$certuser]
GRANT INSERT ON Bulktable TO [reload_table$certuser]

The procedure also needs the server level permission ADMINISTER BULK OPERATIONS to run the BULK INSERT statement. Open the script GrantPermsToSP_server.sql which we learnt about in chapter five in the main article and change the opening to read as below. (This is included in the script for the chapter as a comment to remind you.)

DECLARE @database nvarchar(260) = 'PlayContainer',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES ('ADMINISTER BULK OPERATIONS')

I don't repeat the output here, as it is quite verbose, but you are encouraged to take a look at it as a repetition of how the technique works.

This concludes the setup.

Problem and Solution

Father McKenzie is eager to try the new procedure:

EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT

But he is not permitted to use BULK INSERT, despite the certificate. Here is the output:

There are two cryptic SID tokens. The first that starts with S-1-9-3 is Father McKenzie himself (recall that he exists in the database only), whereas the token that starts with S-1-9-1 is for the database certificate reload_table$cert which has no function on server level, but appears among the login tokens anyway.

If we look at the output from sys.login_token, we can see why he gets the error message: the usage for all tokens reads DENY ONLY. This is not really any new. We saw the same in chapter six in the main article when we looked at an application login impersonating the users with EXECUTE AS USER. If you think that EXECUTE AS is fooling us, you could open a separate query window where you log in as Father McKenzie using the password No one was saved and run reload_table from this window. (Keep in mind that you must specify the PlayContainer database explicitly in the Options tab in the connection dialog in SSMS, since it is only this database that can permit McKenzie to log in.) You will find that the output is the same.

The root problem is that the principal we use is only authenticated on database level. The fact that the procedure is signed with a certificate with server-level permission does not change that. Father McKenzie can still not act outside the database.

So how can we solve this? If the local power user and the server-level DBA is the same person, and there is no one in the database who can create and impersonate users who is not already a member of sysadmin, there is always the TRUSTWORTHY setting:

USE master
go
ALTER DATABASE PlayContainer SET TRUSTWORTHY ON 
GRANT AUTHENTICATE SERVER TO PlayContainer$owner
go
USE PlayContainer
EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT

This time, execution is successful, and we see this output:

PlayContainer$owner appears among the login tokens as AUTHENTICATOR. Since it was granted AUTHENTICATE SERVER, it is trusted to authenticate the database users on server level.

It's not included in the script, but maybe you get the idea to run reload_table from the window where you logged in as McKenzie. You may be surprised to see that this fails, but if you log in anew, you will find that reload_table runs successfully. The explanation for this puzzle is that authentication is something that happens once for all when you log in, so the change of the TRUSTWORTHY setting does not affect existing connections.

However, we don't want to use TRUSTWORTHY, and we want to find a different solution. AUTHENTICATE SERVER is a permission, so why can't we grant it to the certificate login?

USE master
go
ALTER DATABASE PlayContainer SET TRUSTWORTHY OFF
REVOKE AUTHENTICATE SERVER TO PlayContainer$owner
GRANT AUTHENTICATE SERVER TO "SIGN [PlayContainer].[dbo].[reload_table]"

Father McKenzie gives it a shot:

USE PlayContainer
EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT

No, that did not work out. The result is the same as we got initially. Yet we are on the right track. But AUTHENTICATE SERVER is only meaningful if there is something to authenticate. That is, authentication only occurs when a new user context is created. And when McKenzie invokes reload_table, he has already been authenticated. We must introduce an EXECUTE AS clause with a proxy user for this authentication to take place. And since we must have a proxy user, we can just as well grant the proxy user the database permissions needed, and skip the database-local certificate.

CREATE USER reload_table$proxy WITHOUT LOGIN
GRANT INSERT, ALTER ON Bulktable TO reload_table$proxy
go
ALTER PROCEDURE reload_table WITH EXECUTE AS 'reload_table$proxy' AS
   SELECT name, usage, type FROM sys.login_token
   SELECT name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   EXEC ('BULK INSERT Bulktable FROM ''C:\temp\bulktest.txt''
          WITH (DATAFILETYPE = ''char'',
                FIELDTERMINATOR = '';'')')
   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id

Then we modify the contents in the window where we have GrantPermsToSP_server.sql to include AUTHENTICATE SERVER among the permissions (recall that this script will drop the existing certificate and login SIGN [PlayContainer].[dbo].[reload_table] and create new ones):

DECLARE @database nvarchar(260) = 'PlayContainer',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('ADMINISTER BULK OPERATIONS'),
   ('AUTHENTICATE SERVER')

Father McKenzie is willing to make a new attempt:

EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT

He is successful. The output is:

We can see that SIGN [PlayContainer].[dbo].[reload_table] now serves as the authenticator, and thereby authenticating the other tokens on server level.

Just as a reminder: when you use the EXECUTE AS clause, any existing auditing based on USER, SYSTEM_USER etc stops working, but you need to rely on one of original_login and session context / context info.

There is one more thing to observe. What happens here is that thanks to the certificate, the context change incurred by the EXECUTE AS clause is authenticated on server level. Normally, when you sign a procedure, the powers of the certificate remain inside the procedure and is not carried into other modules. This is not really true here, as the authenticated context remains authenticated when execution enters other modules. Therefore, it is important that the user in the EXECUTE AS clause does not have any server powers on its own – nor can be changed to have it. For this reason, as a server-level DBA, you must require that the user in the EXECUTE AS clause is a user created WITHOUT LOGIN. You can use this statement to verify:

ALTER USER reload_table$proxy WITH LOGIN = somelogin

This should yield the error message:

Provided that the database is owned by a non-privileged login you can also accept EXECUTE AS 'dbo', and you may even prefer it, as you may find it easier to validate. If the procedure is owned by dbo, you can also accept EXECUTE AS OWNER If the power user would attempt to change the owner the procedure later on, this incurs no security risk, as this invalidates the signature.

Clean-up

Here is the clean-up code for this chapter. Note that is resets the setting for contained database authentication. Don't run that statement if you want to keep that setting. (Note that this statement will fail, if you logged in as McKenzie in a query window and still have it open.)

USE master
go
ALTER DATABASE PlayContainer SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PlayContainer
go
EXEC xp_cmdshell 'DEL C:\temp\bulktest.txt'
go
DROP LOGIN "SIGN [PlayContainer].[dbo].[reload_table]"
go
DROP LOGIN PlayContainer$owner
go
DROP CERTIFICATE "SIGN [PlayContainer].[dbo].[reload_table]"
go
EXEC sp_configure 'contained database authentication', 0
RECONFIGURE

Letting Users Start Specific Jobs

In this chapter we will look how you can let users start a specific job. There can be more than one reason why you would like do this. It could be a job that is part of an application and any user in the database should be able to start the job. Or it could be a maintenance task that power users should be able to run for their database. Given the diversity, I will present no less than four solutions. Maybe none of them will fit exactly with your situation, but hopefully they can serve as inspiration for devising something that fits you.

The prerequisite for the first solution is that you have read the first five chapters in the main article. For the remaining solutions, you should also have read chapters six and seven.

The script for this chapter is found in app_startjob.sql. Make sure that you have SQL Agent running when you try the script. Since Express Edition does not support SQL Agent, you cannot run this script on SQL Express.

The Setup

We start with creating a database, following the regular procedures we have established

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

Next, we create login for Doctor Robert and then make him a plain user in the PlayJob database with the right to run stored procedures in the dbo schema through membership in JobRole. We also create a login PlayJobAppLogin that we also make a member of JobRole.

CREATE LOGIN DrRobert WITH PASSWORD = 'He does everything he can'
CREATE LOGIN PlayJobAppLogin WITH PASSWORD = 'Here, There and Everythere'
go 
USE PlayJob
go
CREATE ROLE JobRole
CREATE USER DrRobert
CREATE USER PlayJobAppLogin
ALTER ROLE JobRole ADD MEMBER DrRobert
ALTER ROLE JobRole ADD MEMBER PlayJobAppLogin
GRANT EXECUTE ON SCHEMA::dbo TO JobRole

We also need a table to play with and we insert some data into it:

CREATE TABLE Jobtable(id      int          NOT NULL,
                      somedata varchar(40) NOT NULL,
                       CONSTRAINT pk_Jobtable PRIMARY KEY (id)
)
INSERT Jobtable (id, somedata) 
    VALUES (1, 'I''ve got blisters on my fingers!'),
           (2, 'With a Little Help from My Friends')

We need a test job. We will use a job that has a single job step, which runs this fairly non-sensical procedure:

CREATE PROCEDURE SillyJob AS
   UPDATE Jobtable 
   SET    id += 10, 
          somedata = reverse(somedata)

That is, the procedure increases the id column by 10 and reverses the text in the column somedata. This helps us to verify that the job has actually executed. The script below creates the job which is also named PlayJob. The script is a slightly modified version of what I got from SSMS when I scripted my original job.

USE msdb
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name 
               FROM   msdb.dbo.syscategories 
               WHERE  name=N'[Uncategorized (Local)]' 
                 AND  category_class=1)
BEGIN
   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', 
                                               @type=N'LOCAL', 
                                               @name=N'[Uncategorized (Local)]'
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'PlayJob', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'No description available.', 
      @category_name=N'[Uncategorized (Local)]', 
      @owner_login_name='PlayJobAppLogin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
      @job_id=@jobId, 
      @step_name=N'PlayJobStep', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'EXEC SillyJob', 
      @database_name=N'PlayJob', 
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, 
                                         @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, 
                                             @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Pay attention to the output. If you see this message:

SQLServerAgent is not currently running so it cannot be notified of this action.

You need to start Agent for your instance, so that the job can run.

I like to comment on the parameter @owner_login_name to sp_add_job. I set the owner of the job to be PlayJobAppLogin, which is a change from the original script, which has NULL meaning "current user". That is, you, and you are likely to be sysadmin. As Matt Martin made me aware of, a job that runs code that is under control of local power users must never be owned by sa or someone else with sysadmin permission, since the job executes in the context of the job owner. A local power user could add a trigger on Jobtable and that trigger would then execute as sysadmin and could do all sorts of dirty things. For this reason, the job owner needs to be changed to something less powerful, but it should not be a login tied a person (who could leave the company all of a sudden). Here, I am assuming that there already is an application login that we can use. If you don't have this, you would to create an SQL login for the purpose of running application code. But you would not use the database owner – that is still too powerful. For a longer discussion on this topic, see the section Agent Jobs in my article Don't Let Your Permissions be Hijacked!

As the final step of the setup, we move back to the PlayJob database where we create a stored procedure that starts the job PlayJob, waits ten seconds for the job to complete before it displays the contents in Jobtable. In order to verify that we have everything set up correctly, we first run the procedure as ourselves.

USE PlayJob
go
CREATE PROCEDURE start_playjob AS
   EXEC msdb.dbo.sp_start_job 'PlayJob'
   WAITFOR DELAY '00:00:10'
   SELECT id, somedata FROM Jobtable ORDER BY id
go
EXEC start_playjob

You should see this output:

First Solution: Using Certificates Only

When Doctor Robert tries to run start_playjob, there is an error message

EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT

The output:

There is no reason to grant Doctor Robert, directly or through a role, permission to run sp_start_job. After all, PlayJob is the only job he should be able to start, presumably because it performs some background task that is part of the application that runs in the PlayJob database.

Note: you might have expected Doctor Robert to be stopped one step earlier, since we have not created a user for him in msdb. However, this is not necessary, since by default the guest user is enabled in msdb.

Instead we want to solve this with certificate signing. Since what we need to bundle in start_playjob are permissions in the msdb database, the recipe would be to similar to when we packaged server-level permissions in a stored procedure in a user database. But instead of a certificate in master and a login, we need a certificate in msdb as well as an associated user that we grant that the permissions needed. Obviously, we also need the certificate in PlayJob to be able sign start_playjob with this certificate.

When granting the permissions to the certificate user, we should observe that EXECUTE permission on sp_start_job is not enough. We read the topic for sp_start_job in Books Online and learn that in order to be permitted to start a job you don't own yourself, you must be member of the role SqlAgentOperatorRole. This leads to:

USE msdb
go
CREATE CERTIFICATE [SIGN PlayJob_start_playjob] 
ENCRYPTION BY PASSWORD = 'Eight Days a Week'
WITH SUBJECT = '"EXEC ON sp_start_job - SQLAgentOperatorRole"'

CREATE USER [SIGN PlayJob_start_playjob] 
    FROM CERTIFICATE [SIGN PlayJob_start_playjob]

GRANT EXECUTE ON sp_start_job TO [SIGN PlayJob_start_playjob]
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [SIGN PlayJob_start_playjob]

The careful reader may observe that there is one more difference from the recipe used for server-level permission. We actually start in msdb and not in PlayJob. This as such is of no importance, we could have started in PlayJob just as well. But for reasons that will prevail, we need the private key in both msdb and PlayJob, so whereever we start, we need to copy both the public key and the private key. To the latter end, we can use the function certprivatekey. This function requires two passwords. One is the current password of the certificate, the second is the password to protect it in the hex string we get. Recall that a private key must always be protected. When we import the certificate into PlayJob, we need to use a form of CREATE CERTIFICATE FROM BINARY that also accepts the private key, and again we need to give two passwords. One to retrieve it from our hex string and one to protect in PlayJob. And while we could use different passwords here, we keep it simple and stick to the one and the same.

Here is the script. Recall that CREATE CERTIFICATE FROM BINARY does not accept variables. Whence the use of dynamic SQL.

USE msdb
go
DECLARE @public_key varbinary(MAX) = 
            certencoded(cert_id('SIGN PlayJob_start_playjob')),
        @private_key varbinary(MAX) = 
            certprivatekey(cert_id('SIGN PlayJob_start_playjob'), 
                           'Eight Days a Week', 
                           'Eight Days a Week'),
        @sql nvarchar(MAX)

--SELECT @public_key, @private_key

SELECT @sql = 
   'CREATE CERTIFICATE [SIGN PlayJob_start_playjob]
    FROM BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
    WITH PRIVATE KEY
        (BINARY = ' + convert(varchar(MAX), @private_key, 1) + ',
         DECRYPTION BY PASSWORD = ''Eight Days a Week'',
         ENCRYPTION BY PASSWORD = ''Eight Days a Week'')'

PRINT convert(varchar(MAX), @sql)

EXEC PlayJob.sys.sp_executesql @sql

Note that rather than switching to PlayJob with the USE statement, we invoke sp_executesql with three-part notation, which achieves the same result, i.e., @sql is executed in PlayJob.

The output is this CREATE CERTIFICATE statement (with the literals truncated):

CREATE CERTIFICATE [SIGN PlayJob_start_playjob]
    FROM BINARY = 0x30820245308201AEA0030201020210318E...
    WITH PRIVATE KEY
        (BINARY = 0x1EF1B5B000000000010000000100000010...
         DECRYPTION BY PASSWORD = 'Eight Days a Week',
         ENCRYPTION BY PASSWORD = 'Eight Days a Week')

Note: Beware that if you are doing this for real, make sure you do not run with debug output. The CREATE CERTIFICATE command above includes both the private key and the password that protects it. Thus, a malicious user that somehow gains access to the output, could use the command to create the certificate elsewhere and exploit the powers of it.

We can now sign our procedure:

USE PlayJob
go
ADD SIGNATURE TO start_playjob BY CERTIFICATE [SIGN PlayJob_start_playjob]
    WITH PASSWORD = 'Eight Days a Week' 

Would this be sufficient? It seems reasonable to think so, since we have learnt that the powers of the certificate are brought into system procedures, and we are calling a system procedure. Encouraged by this fact, Doctor Robert tries it out:

EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT

Alas, there is an error message, albeit different from before, and the data in the table remains reversed from our initial test:

The reason for this error message is simply that our assumption was wrong. sp_start_job does not count as a system procedure in this context, but it is just like any other user procedure.

How to solve this? Should we sign sp_start_job as well? Wait a minute! If we would do this, anyone with permission to run sp_start_job can start any job, so that can't be right. Still that is the solution. To wit, this is one of the few situations when countersignatures are useful. We discussed these briefly in the end of chapter four. When you countersign a procedure with a certificate, this has no effect when the procedure is called directly. However, if the procedure is called from another procedure which has been signed by the certificate in a normal way, the token of the certificate user will be retained when the countersigned procedure is entered, and thus the packaged permissions are still applicable.

There is one more thing. If you look at the above, you see that the error message does not come from sp_start_job, but from a procedure which sp_start_job calls. In total we need to countersign three procedures:

USE msdb
go
ADD COUNTER SIGNATURE TO sp_start_job 
   BY CERTIFICATE [SIGN PlayJob_start_playjob] WITH PASSWORD = 'Eight Days a Week'
ADD COUNTER SIGNATURE TO sp_sqlagent_notify 
   BY CERTIFICATE [SIGN PlayJob_start_playjob] WITH PASSWORD = 'Eight Days a Week'
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers 
   BY CERTIFICATE [SIGN PlayJob_start_playjob] WITH PASSWORD = 'Eight Days a Week'

Doctor Robert makes a new attempt:

USE PlayJob
go
EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go                    
REVERT

This time he can claim success:

Before we take this to an end, let's prove that the countersignatures hold their promise, by letting Doctor Robert run sp_start_job directly, after first having temporarily granted EXECUTE permission to public.

USE msdb
GRANT EXECUTE ON sp_start_job TO public
EXECUTE AS LOGIN = 'DrRobert'
EXEC sp_start_job 'PlayJob'
go
REVERT
REVOKE EXECUTE ON sp_start_job FROM public

The error message is the same as when we called start_playjob before adding the countersignatures:

While this solution works, you may ask yourself: is it really proper to sign procedures in msdb? Does Microsoft support this? I have not been able to find any statement that resolves this question in either direction, but my gut feeling is that this is not the best of ideas. If nothing else, every time you install a service pack or a cumulative update, it may include a new version of any of the three procedures we signed above – sp_start_job, sp_sqlagent_notify and sp_verify_job_identifiers – in which case the signature will be lost, and users are no longer able to start the job. Here is the critical question: will you remember what you did two years earlier and understand that you need to reapply the countersignatures when things have stopped working? And if you have left the company, will your successor know what to do?

Thus, if you take this road, you need to compose a script similar to GrantPermsToSP_server.sql that drops existing certificates and signatures, creates new certificates and signs the procedures in the user database as well as the procedures in msdb. (The reason you should drop existing signatures is of course that you work with throw-away passwords.) You need to put this procedure on a checklist of things to run after installation of any update (SP, CU or security hotfix) to the SQL Server instance. Still, if you have umpteen servers in your environment, and it is a single database on a single instance that needs this, it is a burden to remember.

So I am strongly leaning towards that the other solutions I will present are better choices. One reason you would prefer to go with the signing solution could be that is important for you to see in sysjobhistory who actually started the job. Run this query:

SELECT * 
FROM   msdb.dbo.sysjobhistory
WHERE  job_id IN (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = 'PlayJob')

In the message column there is a text like this:

The job succeeded. The Job was invoked by User DrRobert. The last step to run was step 1 (PlayJobStep).

That is, the user name that is logged to the table is taken from SYSTEM_USER or any other of those functions of which the output changes when impersonation is in force. (Had Agent taken the value from original_login(), you would have seen your own login since you impersonated DrRobert.) All the other solutions I will present use impersonation and will therefore not display the user correctly.

Before we move on, clean up all signatures and permissions in msdb. However, we keep the certificate as will use it again later.

USE msdb
go
DROP COUNTER SIGNATURE FROM sp_start_job 
     BY CERTIFICATE [SIGN PlayJob_start_playjob]
DROP COUNTER SIGNATURE FROM sp_sqlagent_notify 
     BY CERTIFICATE [SIGN PlayJob_start_playjob]
DROP COUNTER SIGNATURE FROM sp_verify_job_identifiers 
     BY CERTIFICATE [SIGN PlayJob_start_playjob]
REVOKE EXECUTE ON sp_start_job FROM [SIGN PlayJob_start_playjob]
ALTER ROLE SQLAgentOperatorRole DROP MEMBER [SIGN PlayJob_start_playjob]

Second Solution: Stored Procedure in msdb Only

The remaining solutions all use impersonation in one way or another. They are designed with different scenarios in mind. For the first solution we create a login-less user in msdb that will serve as a proxy user which we grant the permissions needed to start jobs:

USE msdb
go
CREATE USER jobstartuser WITHOUT LOGIN
GRANT EXECUTE ON sp_start_job TO jobstartuser
ALTER ROLE SQLAgentOperatorRole ADD MEMBER jobstartuser

Next we create a wrapper procedure on sp_start_job, which accepts a job name as parameter. It checks the input against a list of hard-coded job names (which currently only includes PlayJob), and raises an error if the job is not approved. This procedure executes as jobstartuser:

CREATE PROCEDURE start_job_wrapper @name sysname 
   WITH EXECUTE AS 'jobstartuser' AS
   IF @name IN ('PlayJob')
      EXEC sp_start_job @name
   ELSE 
      RAISERROR('Attempt to start unauthorised job "%s".', 16, 1, @name)

Finally, we create a role with permission to run this procedure, and we add Doctor Robert as a user in msdb and as a member in this role:

CREATE ROLE PlayJobStarters
GRANT EXECUTE ON start_job_wrapper TO PlayJobStarters
CREATE USER DrRobert 
ALTER ROLE PlayJobStarters ADD MEMBER DrRobert

We can now test this from the PlayJob database. Since we are not using the procedure start_playjob, I've added the WAITFOR and SELECT on Jobtable to the script itself, after the REVERT:

USE PlayJob
go
EXECUTE AS LOGIN = 'DrRobert'
EXEC msdb.dbo.start_job_wrapper 'PlayJob'
go
REVERT
WAITFOR DELAY '00:00:10'
SELECT id, somedata FROM Jobtable ORDER BY id

There are no errors, and the output confirms that the job ran:

This solution is intended for the case where you only want selected users to be able to start the job(s) listed in start_job_wrapper and you want to control who they are. For instance, this could be used to permit power users to start maintenance jobs for defragmentation etc in their specific database. On the other hand, this solution is not suitable in the case where all users in a database should be able to start a job which is part of an application, since you need to administer users in msdb.

Above, I only have a cheesy IN clause to check the job name, but you could have a table which says which users (or which roles) that are permitted to start which jobs.

Third Solution: Calling Wrapper in msdb from User Database

The third solution also uses start_job_wrapper in msdb. But instead of adding users in msdb, we call the procedure from start_playjob in the PlayJob database. We cater for the execution permission by signing start_playjob with a certificate of which the user has been granted rights to execute start_job_wrapper.

To show this solution properly, I should start with creating a certificate and all that, but since I don't have the corresponding to GrantPermsToSP_server.sql for msdb available, I am lazy and reuse the certificate we created for the first solution.

Here are the steps we take in msdb, which also includes cleaning up after the previous solution:

USE msdb
go
DROP USER DrRobert
DROP ROLE PlayJobStarters
GRANT EXECUTE ON start_job_wrapper TO [SIGN PlayJob_start_playjob]

Then we go to PlayJob to change start_playjob and sign it with the certificate:

USE PlayJob
go
ALTER PROCEDURE start_playjob AS
    EXEC msdb.dbo.start_job_wrapper 'PlayJob'
    WAITFOR DELAY '00:00:10'
    SELECT id, somedata FROM Jobtable ORDER BY id
go
ADD SIGNATURE TO start_playjob BY CERTIFICATE [SIGN PlayJob_start_playjob]
    WITH PASSWORD = 'Eight Days a Week'

Doctor Robert gives it a shot:

EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT

As it says in the song: no one can succeed like Doctor Robert.

With this solution, as a server-level DBA you no longer have full control over which users that can run start_job_wrapper, but you don't have to administer users in msdb. But if you so fancy, you can still add your own rule table to control which job different users can start. Overall, this solution appears quite palatable to me, as it is fairly simple, and still permits you to have control if you need to.

It goes without saying that if you go this route, you should clone GrantPermsToSP_server.sql to be based on msdb rather than master, including the support for availability groups.

Fourth Solution: Without Code in msdb

You may not like having your own stored procedures in msdb and the fourth and last solution avoids this. In this solution we change jobstartuser to be a proxy login and we impersonate that login in the procedure in the user database. We start with cleaning up from the two previous solutions:

USE msdb
go
DROP PROCEDURE start_job_wrapper
DROP USER jobstartuser

Next, we create jobstartuser as a login, applying the same principles as we use for database owners, that is a random password and we prevent login in two ways. Then we move to msdb and recreate jobstartuser as a user but now mapped to a login, granting the same permissions as previously.

USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN jobstartuser ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN jobstartuser DISABLE
DENY CONNECT SQL TO jobstartuser
go
USE msdb
go
CREATE USER jobstartuser
GRANT EXECUTE ON sp_start_job TO jobstartuser 
ALTER ROLE SQLAgentOperatorRole ADD MEMBER jobstartuser

Next step is to modify start_playjob. We cannot use the EXECUTE AS clause, because we want to impersonate a login, but we can use the statement EXECUTE AS LOGIN. We must also add jobstartuser as a user to PlayJob or else the impersonation will fail.

USE PlayJob
go
CREATE USER jobstartuser
go
ALTER PROCEDURE start_playjob AS
   EXECUTE AS LOGIN = 'jobstartuser'
       EXEC msdb.dbo.sp_start_job 'PlayJob'
   REVERT
   WAITFOR DELAY '00:00:10'
   SELECT id, somedata FROM Jobtable ORDER BY id

For this solution to work, the procedure must be signed with a certificate that comes from master, and where a login has been granted rights to impersonate jobstartuser. We have GrantPermsToSP_server.sql to our disposal, so open this script and change the parameter part to read:

DECLARE @database nvarchar(260) = 'PlayJob',
        @procname nvarchar(520) = 'start_playjob'

-- 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
   ('IMPERSONATE ON LOGIN::jobstartuser')

Run the script, and it is time again for Doctor Robert to try:

EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT

It all runs fine:

Undoubtedly, this is solution is more heavy-weight than the previous one, but you are saved from having code in msdb. You may object that you still need to add a user in msdb, and you want to avoid that to facilitate moving the user database to a different instance. There is a variation of this solution to avoid this, but it breaks the principle of granting the minimum permission needed. That is, you can add jobstartuser to sysadmin, if you want to keep msdb free from all sort of user objects.

I should add that when you as a server-level DBA sign a procedure to permit it to run EXECUTE AS LOGIN, you should insist on that the scope for that impersonation is as short as possible. Specifically, it must not include execution of other stored procedures in the database, as this could permit power users in the database to later add (malicious) code that runs in the context of the impersonated user. Nor should you accept any INSERT, UPDATE, DELETE or MERGE on any table in the database, as this could invoke triggers. Operations on temp tables or table variables, on the other hand, are fine.

Clean-up

Here is a clean-up script for this chapter. Beware that some objects were dropped earlier in the script, so if you grew tired of the chapter and jumped here directly, go back and run the intermediate clean-ups before you run the below.

USE msdb
GO
EXEC msdb.dbo.sp_delete_job @job_name=N'PlayJob', @delete_unused_schedule=1
go
DROP USER jobstartuser
go
DROP USER [SIGN PlayJob_start_playjob]
go
DROP CERTIFICATE [SIGN PlayJob_start_playjob]
go
USE master
go
ALTER DATABASE PlayJob SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PlayJob
go
DROP LOGIN DrRobert
go
DROP LOGIN PlayJobAppLogin
go
DROP LOGIN PlayJob$owner
go
DROP LOGIN "SIGN [PlayJob].[dbo].[start_playjob]"
go
DROP CERTIFICATE "SIGN [PlayJob].[dbo].[start_playjob]"
go
DROP LOGIN jobstartuser
go

Controlling Access to Linked Servers

In this chapter we will look at how you can control access to linked servers. It starts off with a general discussion of the mechanisms available and the security risks they imply. I then go on to describe two solutions based on certificate signing that may be an option when none of the standard alternatives are acceptable to you.

As a minimum prerequisite of this chapter, you should have read the first five chapters in the main article. However, I will make occasional references to later chapters, and I think you will have the best benefit of this chapter if you have read the main article in full.

The script for this chapter is in the file app_linkedserver.sql. When you open this script, you should open it on the instance you plan to use as the remote instance for the exercise, see further in the Setup section. The first section, directly below, does not refer to the script.

Mapping Logins between Local and Remote Instance

On first glance, it may seem that access to a database on a different server is analogous to accessing a different database on the same instance. But in fact, the two situations do not have much in common. In the main article, I devoted a chapter to cross-database access, and I discussed five different approaches:

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.

Of these methods, only the first is directly applicable to cross-server access. There is no guest user on server level and there is no way that certificates can be trusted across servers for this purpose, nor is there any notion of ownership chaining across servers. Why the last method does not work, I will return to.

To access the linked server, the users must somehow be able to connect to the remote data source. The easiest way is to rely on so-called self-mapping, which is also the default. That is, SQL Server attempts to access the remote data source with the same credentials that the user used to connect to the local SQL Server instance. This usually works, if any of the following are true:

• The user connected with Windows authentication and the same login works on the remote server as well. The servers are in the same domain, or there are trust between the domains.
• The user connected with Windows authentication, the servers are in a workgroup, and the user has the same Windows username and password on both machines.
• The user connected with SQL authentication, and the same username and password works on the remote instance as well.

I say "usually", because there can be obstacles. It is not uncommon to run into double-hop issues related to delegation or Kerberos. What applies when the remote data source is not SQL Server, I don't know, as I have no experience of this situation.

The alternative to self-mapping is to set up an explicit mapping. To this end, you use the system procedure sp_addlinkedsrvlogin which takes five parameters: @rmtsrvname, @useself, @locallogin, @rmtuser and @rmtpassword.

@rmtsrvname is the name of the linked server, for instance PLAYSERVER. @locallogin is a login on the local instance to which the mapping applies. You can specify SQL logins as well as Windows logins here. @locallogin can also be NULL, which states that this mapping applies to all logins for which there is no explicit mapping. The default is NULL.

@useself is a varchar parameter, but the only to permitted values are 'true' and 'false', with 'true' being the default. 'true' means that self-mapping is to apply for @locallogin. When you create a new linked server, self-mapping is set up for all users. For instance, if you say:

EXEC sp_addlinkedserver 'PLAYSERVER'

this implies the call:

EXEC sp_addlinkedsrvlogin 'PLAYSERVER', @useself = 'true', @locallogin = NULL

When you specify @useself = 'false', you normally specify a username and password which is valid on the remote server in the parameters @rmtuser and @rmtpassword. You can however, leave them NULL, if you want to deprive @locallogin all access to the remote data source through the linked server.

When the remote server is another SQL Server instance, @rmtuser must be an SQL login on the remote instance (which thus must be configured to permit SQL authentication). Some people make the mistake to specify a Windows user and password, but that will not fly. To log on to SQL Server with Windows authentication, you must first log on to Windows and only then you can log on to SQL Server. But SQL Server cannot not on its own validate a Windows username and password. This applies no matter you are logging into SQL Server through SSMS or over a linked server. Overall, to the remote instance, your local instance of SQL Server is just another client.

It follows from the above that when you set up a mapping to a remote server, that you can do it for one login at a time or for everyone by specifying @locallogin as NULL. The latter is absolutely nothing you should do lightly. You may think that you can restrict which users that can access the linked server, but a linked server as such is not a securable. That is, you cannot grant or deny permissions on the linked server. Once it exists, all users on the instance can access it, and whether they can make use of it depends on whether their login one way or another maps to a login on the remote server. So if you set up a mapping to an @rmtuser for @locallogin = NULL, everyone on the server can access that remote server with the permissions @rmtuser has on that instance. It goes without saying that specifying sa for @rmtuser is an exquisitely bad idea.

It could be acceptable to set up a default mapping to the same remote user for all local users if you have arranged so that users can only access SQL Server from the application, and thus cannot run queries from SSMS, Excel or similar. (I discuss techniques for how to do this in chapter six in the main article.) You may think it would also be OK, if the remote user has very restricted permissions on the remote server, like read-only access to one table of which the contents is not overly confidential. But keep in mind that a malicious user with no permissions beyond connecting to a server still can cause an outage by creating a temp table and adding data to it so that tempdb fills up.

Thus, from a security perspective, it is preferable to set up mappings per user, and only for the users who actually need access to the remote server. But this can easily become an administrative burden as soon as it is a matter of more than a handful users. You may ask if you can mitigate this by setting up a mapping per server roles or Windows AD groups, and the answer is that you cannot. You can only define mappings per individual logins. However, if the access to the remote server is confined to a few places that can be put into stored procedures or user-defined functions, I will suggest solutions later in this chapter.

No matter how you set up the login-mapping, the user on the remote server should have minimal permissions, for instance only SELECT permissions on specific tables or EXECUTE permissions on specific stored procedures. If the remote instance runs SQL 2008 or earlier, this can lead to performance issues if you join local and remote tables in a query. It can also occur on SQL 2016 and later, if row-level filtering in effect. For further discussion on this, see the section An Issue with Linked Servers in my article Slow in the Application, Fast in SSMS.

I suspect that in the case when people set up mapping on per-user basis, they still let all (or most) users map to the same remote user. But say that each user has its own credentials on the remote data source, but in such a way that self-mapping is not possible. (For instance, their usernames or passwords are different.) In this case, you could let the users administer the mapping themselves. They cannot call sp_addlinkedsrvlogin directly, as this procedure requires the permission ALTER ANY LOGIN. However, you can write a wrapper which goes something like this:

CREATE PROCEDURE define_your_user_on_that_server @username sysname,
                                                 @password sysname AS
   EXEC sp_addlinkedsrvlogin 'PLAYSERVER', 'false', SYSTEM_USER,
                              @username, @password

That is, you hardcode the server name (as there is probably only one server you want to do this for), and you force @locallogin to be the name of the current user. You sign this procedure with a certificate, and you create a login from the certificate that you grant ALTER ANY LOGIN. I don't show the details on how to do this, but you learnt this technique in chapter five of the main article and how you easily can do this with the script GrantPermsToSP_server.sql that I presented later in that chapter.

Setup

We will now look at two solutions for access to linked server with help of certificate signing. In the main solution we will package the access to the linked server in a stored procedure, and in an alternate solution we will use a table-valued function. Both solutions relieve us from having to set up a default mapping for all users or a mapping per user. This section only serves to set things up for the demos, and the actual solutions are in the next two sections.

Since we will work with linked servers, the setup for this exercise is more complex than for other chapters. First of all, you will need two instances of SQL Server. I tested the script with two instances running on my local desktop, but it should work if the instances are on different servers as well. As for the rest of the article, the script assumes that the remote instance runs SQL 2012 or later. If you have an earlier version, you will need to make some syntactical changes.

The script features a Windows user by the name of LIVERPOOL\SirPaul. Replace the name with a Windows user you create yourself, or use for the task. LIVERPOOL\SirPaul is supposed to be plain user with no elevated permissions. If it is cumbersome for you to find/create a Windows user to use, you can simply skip those parts as the Windows user is not essential for the demo.

The first part of the setup takes place on the remote server, so when you open the script for this chapter, app_linkedserver.sql, you should connect to the instance you plan to use the remote server (typically not the instance you have run all the other scripts on). We start with creating a database RemotePlay:

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

Next, we create two logins. One Windows login for SirPaul and one SQL login for JohnL. The purpose of these logins will appear as the chapter moves on. (As noted above, replace LIVERPOOL\SirPaul with the actual Windows user you use.)

CREATE LOGIN [LIVERPOOL\SirPaul] FROM WINDOWS
CREATE LOGIN JohnL WITH PASSWORD = 'Revolution #9'

USE RemotePlay
go
CREATE ROLE RemoteRole
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO RemoteRole 
CREATE USER JohnL
CREATE USER [LIVERPOOL\SirPaul]
ALTER ROLE RemoteRole ADD MEMBER JohnL
ALTER ROLE RemoteRole ADD MEMBER [LIVERPOOL\SirPaul]

Finally, we create a table and insert some rows into that table.

CREATE TABLE RemoteTable (id       int          NOT NULL,
                          somedata nvarchar(40) NOT NULL,
                          CONSTRAINT pk_Remote PRIMARY KEY (id))
INSERT RemoteTable(id, somedata)
   VALUES(1, 'Everybody''s Got Something to Hide'),
         (2, 'Except Me and My Monkey')

This concludes the preparations on the remote server. Change your connection and log in to your local instance. You can keep a connection open to the remote instance if you like, but the script will not return to this instance until the very end when it is clean-up time.

On the instance that serves as the local instance, we also need a database, call it LocalPlay:

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

Next, create the linked server. In the snippet below, replace yournamehere with the actual name of the remote instance.

EXEC sp_addlinkedserver 'PLAYSERVER', N'', N'SQLNCLI11',
                        @datasrc = 'yournamehere'

If you already have a linked server set up for the remote instance, leave that linked server alone, as the server may have a configuration that affects the outcome of the exercises. Thus, I want you to run with a freshly created linked server with known behaviour. In case you are not aware of it: a linked server is essentially only an alias, so the name of the linked server does not have to be the same as the actual name of the remote server.

Next, we create a login for Ringo who will serve as our test user in this chapter. We will not use EXECUTE AS to impersonate him. Instead we actually log in as him, so we set his default database.

CREATE LOGIN Ringo WITH PASSWORD = 'Octopus''s Garden', 
                        DEFAULT_DATABASE = LocalPlay

In the LocalPlay database we run our standard configuration by adding a role which we grant EXECUTE permission on the dbo schema and we add Ringo to the database and the role.

USE LocalPlay
go
CREATE ROLE LocalRole
GRANT EXECUTE ON SCHEMA::dbo TO LocalRole
CREATE USER Ringo
ALTER ROLE LocalRole ADD MEMBER Ringo

The last step in the setup is to create a procedure which reads a row from RemoteTable on PLAYSERVER.

CREATE PROCEDURE get_remote_data @id int AS
   SELECT id, somedata
   FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
   WHERE  id = @id

The Main Solution

Open a new query window and log in as Ringo (the password is Octopus's Garden). Keep this window open as we will run as Ringo a few times. Run this statement:

EXEC get_remote_data 2

This produces an error message:

Given that we never added Ringo as a login on PLAYSERVER, this is not very surprising. Self-mapping is in force and the assumption for this exercise is that Ringo and his co-users do not have logins of their own on PLAYSERVER. Nor do we want Ringo to be able to access PLAYSERVER and RemoteTable by any other means than running get_remote_data. How can we achieve this?

Your first thought may be that you can use a login that has access to the remote database, for instance SirPaul. And if you only have read the first half of the main article but you have heard about the EXECUTE AS clause elsewhere, you my be inclined to try:

USE LocalPlay
go
CREATE USER [LIVERPOOL\SirPaul]
go
ALTER PROCEDURE get_remote_data @id int 
   WITH EXECUTE AS 'LIVERPOOL\SirPaul' AS

   SELECT id, somedata
   FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
   WHERE  id = @id

(You may note that the setup never created a login for SirPaul on the local instance. That's alright. You can create a database user for a Windows user, even if there is no login for it on server level on its own or through an AD group. It's sufficient that the user exists in the locally or in the domain.)

You can test this in the window where you are logged in as Ringo:

EXEC get_remote_data 2

The outcome is utterly negative, and we seemed to have made to progress at all:

This may seem puzzling. We are impersonating SirPaul, and yet login fails for Ringo. Didn't the EXECUTE AS work? The answer is Ringo never came that far. This error occurs already during compilation of the procedure. In order to build an execution plan, the optimizer needs to access the remote table to get metadata and statistics and this occurs before the EXECUTE AS clause takes effect. (You may have heard of deferred name resolution and think the optimizer should ignore that it cannot access the table at the point when the procedure starts. But for whatever reason, deferred name resolution does not apply to objects on linked servers.)

One way to address this is to put the query in dynamic SQL:

USE LocalPlay
go
ALTER PROCEDURE get_remote_data @id int 
   WITH EXECUTE AS 'LIVERPOOL\SirPaul' AS

   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT id, somedata
                  FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
                  WHERE  id = @id'
   EXECUTE sp_executesql @sql, N'@id int', @id

Ringo tries again:

EXEC get_remote_data 2

And there is a new error message:

Readers who have read the full article, or at least chapter eight, knew right from the start that it would end this way. When you use the EXECUTE AS clause, you impersonate a database user and you are sandboxed into the current database and cannot access things outside of it, for instance server objects like linked servers.

But what if we impersonate SirPaul as a login with the EXECUTE AS LOGIN statement? Then we would have to sign the procedure with a certificate that has been granted IMPERSONATE rights on SirPaul through a certificate. Here is such a procedure:

ALTER PROCEDURE get_remote_data @id int AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT id, somedata
                  FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
                  WHERE  id = @id'

   EXECUTE AS LOGIN = 'LIVERPOOL\SirPaul'
      EXECUTE sp_executesql @sql, N'@id int', @id
   REVERT

However, we before we go on and sign it and test it as Ringo, let's test it ourselves directly to see that we are on the right track:

EXEC get_remote_data 1

Not really, because we get this error message:

The error message means exactly what it says. Once you have engaged in impersonation in any form, you can no longer rely on self-mapping. And for very good reasons. Say that you are the DBA on YOURSERVER, whereas Evelyn is the DBA on HERSERVER. You don't have any access to HERSERVER, but you are very curious about what is in there. So you create a login for Evelyn's domain user on your server, and then impersonate her. Should you now be able to access HERSERVER? Of course not!

Impersonation in SQL Server follows the principle what happens in Vegas, stays in Vegas. Impersonation of a database user, stays in that database to prevent that a local power user elevates his permission to server level. And impersonation on server level in SQL Server is confined to SQL Server and ignored by Windows, so that a DBA cannot elevate his permission in Windows by impersonating a Windows administrator.

Thus, self-mapping cannot be used for this problem. But what if we set up login mapping, only for a proxy login and impersonate that login in our procedure? There is already a suitable login on PLAYSERVER, to wit JohnL, who has access to RemoteTable. We could use SirPaul as the local proxy login, but that is not a very good idea. One day, Paul gets a login for real on our server and wants to run queries against PLAYSERVER. Certainly, he does not want to be John on PLAYSERVER in that case! (Not talking about the risk that he could leave the company, and be dropped from the AD without anyone telling us.)

Instead we create an SQL login to act as a proxy for the mapping. Since it is a proxy, we deprive it of all possibilities to log in and give it a random password just like those database owners. In this chapter, we call this login GeorgeH.

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

Then we set up login mapping for GeorgeH on PLAYSERVER, using the login of JohnL:

EXEC sp_addlinkedsrvlogin 'PLAYSERVER', 'false', 
                          'GeorgeH', 'JohnL', 'Revolution #9'

We move over to LocalPlay and create a user for GeorgeH and change the procedure accordingly:

USE LocalPlay
go
CREATE USER GeorgeH
go
ALTER PROCEDURE get_remote_data @id int AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT id, somedata
                  FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
                  WHERE  id = @id'
   EXECUTE AS LOGIN = 'GeorgeH'
      EXECUTE sp_executesql @sql, N'@id int', @id
   REVERT

Now it is time to sign the procedure. To this end, we use the script GrantPermsToSP_server.sql, which we learnt about in chapter five of the main article. Change the parameter part to read:

DECLARE @database nvarchar(260) = 'LocalPlay',
        @procname nvarchar(520) = 'get_remote_data'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('IMPERSONATE ON LOGIN::GeorgeH')

The script produces a number of SQL statements in the output. I don't include them here, but I encourage you to study the output to repeat the details of the technique.

Now, move over to the window where you are logged in as Ringo:

EXEC get_remote_data 2

Success!

For our simple query, using dynamic SQL was acceptable. For a more complex query it may be more problematic, since dynamic SQL makes the code more difficult to read and maintain. An alternative is to move the query to an inner procedure and keep the original procedure as a shell procedure that only runs EXECUTE AS LOGIN and then calls the inner procedure. There is however, a security consideration for the server-level DBA here. One of the strengths with certificate signing is that the DBA sees exactly the code he signs and therefore he knows that if the procedure invokes other procedures or triggers that are unsigned, the certificate will not have any effect on these procedures.

But when you sign something to grant impersonation rights it is different, as the effect of impersonation lingers into the other modules as well. Say that the data in RemoteTable is very sensitive and you want to ensure that the query executed stays in within the limits you have agreed on with the RemotePlay DBA and only retrieves data that LocalPlay users are entitled to see. In this case, you cannot accept that the query is pushed into an inner procedure that local power users can change without invalidating the signature. On the other hand, if the query is placed in dynamic SQL in get_remote_data, they cannot change the query and keep the signature and the permission, so they will need to ask you to re-sign it if they fiddle with it.

I also like make a note on the logins JohnL and GeorgeH. I only used these names to comply with the Beatles theme. In real life these would be system accounts not tied to any person, with their names possibly indicating their function. When it comes to the login corresponding to GeorgeH, you would not grant it any permissions whatsoever, except making it a user in databases that need to access the linked server. Also, it is worth pointing out that while there are two places where you need specify the password for the login here called JohnL, you could use compose a script that encompasses both servers and thus permits the password to be a random string that no one ever sees.

An Alternate Solution

I was contacted by a user who had seen my suggestion above, but he wanted to put the access to the linked server in a table-valued function for reasons that will prevail. In a function, we cannot use the EXECUTE AS LOGIN statement, but we can still do impersonation through the EXECUTE AS clause in the function header, like this:

CREATE FUNCTION fun_remote_data(@id int) 
RETURNS @t TABLE (id       int          NOT NULL,
                  somedata nvarchar(40) NOT NULL)
  WITH EXECUTE AS 'GeorgeH' AS
BEGIN 
   INSERT @t (id, somedata)
      SELECT id, somedata
      FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
      WHERE  id = @id
  RETURN
END
go
CREATE PROCEDURE get_remote_data_alt @id int AS
   SELECT id, somedata
   FROM   dbo.fun_remote_data(@id)

We tried this in the previous section as well, and it don't really work out. If we run get_remote_data_alt as ourselves:

EXEC dbo.get_remote_data_alt 1 

we get this error message:

Because: the EXECUTE AS clause impersonates a database user, we are sandboxed into the current database and we cannot access things outside of it, including linked servers.

We learnt in the main article that we can get out from the sandbox up to server level, if the database is marked TRUSTWORTHY and the database owner has the permission AUTHENTICATE SERVER. We also learnt that this is a security risk that permits a database-level DBA to elevate to sysadmin. My correspondent had this solution in place and wanted to move away from it, preferably without rewriting a lot of code. That is, he wanted to keep his function.

Thankfully, it is possible to package the AUTHENTICATE SERVER permission with the function itself by means of certificate signing. This evades the need to mark the database as TRUSTWORTHY, and nor does the database owner need any particular permission. The login created from the certificate serves as the authenticator for the user in the EXECUTE AS clause on server level.

Thus, in the window where you have GrantPermsToSP_server.sql open, paste this into the parameter section:

DECLARE @database nvarchar(260) = 'LocalPlay',
        @procname nvarchar(520) = 'fun_remote_data'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('AUTHENTICATE SERVER')

Once you have executed the script, you can try running get_remove_data_alt again:

EXEC dbo.get_remote_data_alt 1

Now you will see this output:

Move over to the window where you are logged in as Ringo, and run the procedure there as well:

EXEC dbo.get_remote_data_alt 1

The output is the same.

If you have been a careful reader of this chapter, you may have an uneasy feeling that something is still not quite right here. Indeed, in the window where you are logged in as yourself run this:

EXEC sp_recompile fun_remote_data

Then go back to the window where you are logged in as Ringo, and run the procedure again (do not run it as yourself first!):

EXEC get_remote_data_alt 1

This is the output this time:

We saw this already in the previous section. Because there is no plan for fun_remote_data in the cache, the function must be compiled. But the compilation is performed under Ringo's own security context, and not under the context of GeorgeH, as the EXECUTE AS clause has not taken effect yet. And SQL Server insists on being able to access the remote data source, since there is no deferred name resolution for objects on linked servers. It seemed to work in our first attempt, but that was because we first ran the procedure and function as ourselves. Even if the execution failed because of the sandboxing with EXECUTE AS, the compilation still succeeded as it was performed under own security context and a plan was put into cache which Ringo could reuse.

There is a simple way to work around the problem, the same we employed in the previous section: push the access to the linked server to an inner scope, so that the compilation is always performed under the context of GeorgeH. This is not entirely palatable, as we now store the data twice in intermediate return tables. (And, yes, they have to be multi-statement functions; inline functions do not accept EXECUTE AS.) I'm not showing the statements, but if you want to see it in action, there is code to try in the demo script for this chapter.

As I discussed at the end of the previous section, this means that as the server-level DBA you lose control. You sign the outer function, and it cannot be changed with less than the signature is invalidated and the packaged permission disappears. But the database DBA can change the inner function without affecting the signature. And not only that: the database DBA can change the user mapping for GeorgeH so that it maps to a login with higher powers, for instance sa. This means the database DBA can write a function that retrieves data from anywhere on the server in that inner function, thanks to the blessing with AUTHENTICATE SERVER on the outer function.

The conclusion is that this alternate solution is only permissible in the situation when everyone with permission to change the inner function already are in the sysadmin role. As soon as there are someone who only has permissions inside the database, but still powers to change the function, for instance being a member of db_ddladmin, I recommend against this approach.

Note: My correspondent asked if it is possible sign the inner function in such a way that the outer function could detected that the inner function has been tampered with. I believe that you could use the function is_objectsigned for this purpose, but as I find that a quite cumbersome solution, I don't take up space with it here, but leave it to the reader to explore on your own.

Conclusion and Clean-up

We have now looked at the options for access to linked servers, and essentially there are five options:

1. Self-mapping.
2. Mapping to an explicit user, used by all logins on the server.
3. Mapping to an explicit user, configured login by login.
4. Using EXECUTE AS LOGIN for a login which has a mapping to the remote server, packaged in a stored procedure which has been signed with a certificate with IMPERSONATE permission on the login.
5. Using the EXECUTE AS clause in a function for a user that maps to a login with a mapping to the remote server, and the function is signed with a certificate with AUTHENTICATE SERVER permission. This is a lesser preferred option for reasons discussed in the previous section.

Here is the clean-up to run on your local server:

USE master
go
ALTER DATABASE LocalPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE LocalPlay
go
EXEC sp_droplinkedsrvlogin 'PLAYSERVER', 'GeorgeH'
go
DROP LOGIN LocalPlay$owner
go
DROP LOGIN Ringo
go
DROP LOGIN "SIGN [LocalPlay].[dbo].[get_remote_data]"
go
DROP CERTIFICATE "SIGN [LocalPlay].[dbo].[get_remote_data]"
go
DROP LOGIN GeorgeH
go
EXEC sp_dropserver 'PLAYSERVER'

And here is the clean-up to run on the instance you used as PLAYSERVER. If you never created a login for SirPaul, just let that line produce an error.

ALTER DATABASE RemotePlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE RemotePlay
go
DROP LOGIN [LIVERPOOL\SirPaul]
go
DROP LOGIN JohnL
go
DROP LOGIN RemotePlay$owner

Loading CLR Objects

In this chapter, we will look at a way to load CLR objects (stored procedures, functions etc) with a stored procedure that accepts the path to the source file as input and which uses a way to trust assemblies that was introduced in SQL 2017. I am not going to tout this as the ultimate way to load assemblies, but rather it is something I provide as food for thought, and if you like the idea, you can refine it further.

The prerequisite for this chapter is that you have read first five chapters of the main article. I also assume that you have worked with development and deployment of CLR objects in SQL Server before.

The script for this chapter is app_load_assemblies.sql. In contrast to all other scripts in this series, it requires SQL 2017 and does not run on lower versions. However, it takes a while until we come to look at the script, as the chapter starts with a look at the general challenges of using the CLR in SQL Server. This is followed of a textual overview of the solution before I go into a detailed look at the code and some test examples. At the end of the chapter, I have a concluding discussion of how the solution could be improved.

The Challenges with the CLR

The CLR is one of these features in SQL Server that has never really taken off. Some sites use it heavily, but in many other places it is not in use at all. One reason is that developing and deploying CLR objects is more cumbersome than regular SQL objects. Even if you only have a safe unprivileged assembly, there are a number of steps to go through. You have to compile the .NET code and bring the DLL to SQL Server, as a file or as a hex string, to create an assembly from it. Finally, you create the actual procedure or function from the assembly.

If you are developing a larger library for calculations or whatever, this may be alright, but if all you want is a utility procedure of less than hundred lines of code, it becomes a bit of a burden. Also, this process tends to put the DBA in a uncomfortable situation, since often the DBA only sees the compiled DLL, and therefore is not able to review what's in it. (Never mind that the DBA may not be very good at reading .NET code in the first place, but he could request help from a trusted colleague.)

If you have an assembly that needs EXTERNAL ACCESS or UNSAFE permission, the number of steps increases, as the assembly has to be signed with a key that is installed in master. A login created from that key must be granted the matching permission (EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY). Handling that key comes with its own complexity. You can create a key for each user you entrust to create privileged assemblies, so that they can sign their assemblies. These persons in their turn need to keep their key protected so that it does not leak to untrusted developers. You can evade the key-management chores by having a deployment script which creates a new key on every deployment in the same spirit as I suggest for certificate signing in the main article, but it's not entirely straight-forward.

Yet an alternative is to make the database TRUSTWORTHY and grant the database owner the permission needed. But if you are not the power user of that database as well, that deprives you control of which users that can create unsafe assemblies and that is not unimportant, given that a poorly written unsafe assembly can threaten server stability. Also, do not forget that a skilled user can use an unsafe assembly to elevate his permission to sysadmin. Well, if the database is owned by sa (or someone else who is sysadmin), the TRUSTWORTHY setting alone permits local power users to easily elevate to sysadmin, without an assembly. (See the section The Dangers of TRUSTWORTHY in the main article for a longer discussion on this.) Thus, this is rarely an acceptable option.

The division into safe and unsafe assemblies relies on something known as Code Access Security (CAS) in .NET, and starting with .NET 4, which is what SQL Server uses since SQL 2012, CAS is no longer supported as a security boundary. (This blog post from Shawn Farkas has some details on this). That is, a skilled developer might be able to craft an assembly which performs unsafe operations, but which is nevertheless accepted as a safe assembly by SQL Server. Not being any sort of expert in .NET, I cannot say how this would be done or whether it actually is possible to exploit. But my general understanding of security issues tells me that if this is something that can be exploited, the people out there who making a living from hacking sites will find a way exploit it to add more attack vectors to their portfolio. (For instance, imagine a website which is open for SQL injection and where the application login has db_owner rights, but no server-level permission. An attacker could use this to create a malicious assembly to gain sysadmin rights to infect the server further.)

Somewhat belated, Microsoft has addressed this by introducing CLR Strict Security. In SQL 2017 and later there is a configuration option clr strict security which is enabled by default when you make a fresh install of SQL 2017. (But if you upgrade an instance from SQL 2016 or earlier, the setting defaults to disabled.) CLR Strict Security is also available on SQL 2012, SQL 2014 and SQL 2016 provided that you are on a patch level recent enough as described in KB article 4018930. There is no configuration option in these versions, but instead you enable CLR Strict Security through trace flag 6545. (Note that it must be given as a start-up parameter; you cannot enable or disable it with DBCC TRACEON or TRACEOFF.) Microsoft's recommendation is that CLR Strict Security should be enabled.

When CLR Strict Security is in force, all user-created assemblies are considered to be unsafe, and this includes the assemblies that were loaded on the server prior to the setting went in force. This does not mean that the clause WITH PERMISSION_SET loses its meaning. An assembly that performs an action that is known to be unsafe, for instance invokes native code, must still be labelled as UNSAFE, so it is still works a protection against pure folly.

Since this means that the permission UNSAFE ASSEMBLY now must be present for all assemblies, by any of the means mentioned above, this raises the hurdle for using the CLR further, particularly if you in the past only worked with safe assemblies. However, the situation is not as dire as it may seem at first, because to mitigate the situation, Microsoft introduced a new way to trust assemblies in SQL 2017 that saves you from signing assemblies and managing keys. You can use the new stored procedure sp_add_trusted_assembly to enter an SHA2_512 hash of the assembly to mark it as trusted. The direct advantage is that it facilitates the handling of the assemblies you have used and loved for many years – and presumably have full trust in. You can iterate over all databases, extract the assembly bits with assemblyproperty to compute a hash with the hashbytes function in SQL Server and load the hash. But as we shall see, this new stored procedure also permits for a solution that makes it easier to load new or changed assemblies into SQL Server.

To run this procedure, you need the permission CONTROL SERVER or membership in sysadmin. Note that this new procedure has not been backported to SQL 2016 and earlier versions.

Overview of the Solution

I will now present a solution to many of the problems/obstacles covered in the previous section. The solution is a stored procedure sp_play_load_assembly. As I said in the introduction of this chapter, this is something that it is intended to be food for thought and something you can refine to fit your needs and the way you work. I should add that it is mainly intended for simple utility CLR stored procedures and functions of 100 lines of code or so. It is unlikely you would use it a for a larger library that consists of multiple source files, for instance the implementation of a versatile CLR user-defined data type.

The short summary of what the procedure does is this: it takes the name of a source file as a parameter. This procedure is compiled. The assembly is loaded into a variable with OPENROWSET(BULK), and the procedure computes the hash for the assembly. The hash is registered with sp_add_trusted_assembly whereupon the procedure creates the assembly with the requested name and permission set; both these two are parameters to the procedure. As a last step, the procedure cleans up hashes for older versions of the assembly.

To keep things simple, I only support C#, but if you prefer Visual Basic or some other .NET language, you can add this.

If I wanted only the server-level DBA to be able to run this procedure, I could have stopped there. However, there may be persons in your organisation you trust not to be malicious and you therefore may want to permit to load assemblies. Then again, you may not trust of all these people to know exactly what they are doing. That is, there may be users you only want to permit to create safe assemblies, whereas you may trust more senior developers to be qualified to write all sorts of assemblies. Your assumption here is that the risk is low that someone would unknowingly introduce unsafe code in a purportedly safe assembly.

For this reason, the procedure uses two security devices:

1. The procedure relies on three server roles: SafeAssemblyLoaders, ExtAccessAssemblyLoaders and UnsafeAssemblyLoaders. To load an assembly with a certain permission set, the login must be a member of the corresponding role (or have CONTROL SERVER permission). To facilitate adding logins to these roles, membership in any of the latter two implies membership in SafeAssemblyLoaders.
2. To permit users without server-level permission running sp_add_trusted_assembly, the procedure is signed with a certificate and a login created from that certificate is granted CONTROL SERVER.

As you might guess from the name, the procedure is intended to reside in master. This permits users to invoke the procedure from any database and it will execute in the context of that database. I like to re-emphasise what I said when I introduced sp_ShowSessions in the main article: this is something that is entirely undocumented and unsupported and it could break at any time. Particularly, Microsoft could decide to ship a system procedure with the same name. It is to reduce this risk of collision, I've added the play string to the name. If you are uncomfortable with home-brewed system procedures, rename the procedure and put it in user your databases instead.

The Solution in Detail

We will now look at the code for the procedure. In case you have forgotten it, the script is in app_load_assemblies.sql. We start with creating the three server roles. We add two more powerful roles as a member to the third, so that no one is only able to create unsafe assemblies.

SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE SERVER ROLE SafeAssemblyLoaders
CREATE SERVER ROLE ExtAccessAssemblyLoaders
CREATE SERVER ROLE UnsafeAssemblyLoaders
ALTER SERVER ROLE SafeAssemblyLoaders ADD MEMBER UnsafeAssemblyLoaders
ALTER SERVER ROLE SafeAssemblyLoaders ADD MEMBER ExtAccessAssemblyLoaders

Before we come to the main procedure, there is a sub-procedure, sp_play_check_permission to look at. This procedure checks two things:

1. That the user has CREATE ASSEMBLY permission in the current database.
2. Membership the in matching server role or CONTROL SERVER permission.

CREATE OR ALTER PROCEDURE sp_play_check_permission 
                          @permission_set varchar(20) AS

   DECLARE @db sysname = db_name()
   IF isnull(has_perms_by_name(@db, 'DATABASE', 'CREATE ASSEMBLY'), 0) = 0
   BEGIN
      RAISERROR('You do not have CREATE ASSEMBLY permission in database "%s".', 
                16, 1, @db)
   END

   IF has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 0 AND
      isnull(
         is_srvrolemember(CASE upper(@permission_set)
                               WHEN 'SAFE'            THEN 'Safe'
                               WHEN 'EXTERNAL ACCESS' THEN 'ExtAccess'
                               WHEN 'UNSAFE'          THEN 'Unsafe'
                          END + 'AssemblyLoaders', SYSTEM_USER), 0) = 0
   BEGIN
      RAISERROR('You do not have permission to load %s assemblies',
                16, 1, @permission_set)
   END

The reason is that this code is in a sub-procedure is that the calls to has_perms_by_name would always return 1 if they were in sp_play_load_assembly directly, since this procedure is signed with a certificate that gives it CONTROL SERVER (which implies CREATE ASSEMBLY permission in the database). Here we make use of the fact that when you call an inner procedure, the token of the certificate login is removed and thus the permission. (You may recall that this does not apply to system procedures, but thankfully our home-written system procedure does not count as one in this context.)

Now we arrive at sp_play_load_assembly itself. Since the procedure is a couple lines of code, I will present it in pieces. You find the full version in app_load_assemblies.sql. The script has comments, but I have removed them here, since I discuss the code in the running text.

This is the outer shell of it:

CREATE OR ALTER PROCEDURE sp_play_load_assembly 
                          @srcpath        nvarchar(250),
                          @assemname      sysname,
                          @permission_set varchar(20) = 'SAFE' AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY

   -- For diagnostics only.
   -- SELECT name, usage, type FROM sys.login_token

   ...

END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH

That is, there is standard error handling along the lines I preach in my series Error and Transaction Handling in SQL Server. I have included a diagnostic SELECT from sys.login_token and the same SELECT is also in sp_play_check_permission. I have commented them out, but you are encouraged to run the procedure with the two SELECT activated, so you can see how the certificate is added and removed.

The procedure first validates the parameters @permission_set and @srcpath and then comes the call to sp_play_check_permission.

IF upper(@permission_set) IS NULL OR
   @permission_set NOT IN ('SAFE', 'UNSAFE', 'EXTERNAL ACCESS')
BEGIN 
   RAISERROR('Illegal value for @permission_set: "%s".', 
             16, 1, @permission_set)
   RETURN 1
END

IF @srcpath IS NULL OR lower(right(@srcpath, 3)) <> '.cs'
BEGIN 
   RAISERROR('Illegal value for @srcpath "%s". Does not end in .cs', 
             16, 1, @srcpath)
   RETURN 1
END

EXEC sp_play_check_permission @permission_set

The next step is to set up the path for the assembly and to the C# compiler.

DECLARE @assempath nvarchar(250) = 
       substring(@srcpath, 1, len(@srcpath) - 3) + '.dll'

DECLARE @cscpath nvarchar(250) = 
       'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe'

I believe that most modern Windows installations has a C# compiler in this location, but you may need to change this. Particularly, if you have Visual Studio installed, you may prefer to use the compiler from Visual Studio.

Now it's time to compile the file.

DECLARE @cmd varchar(2000) = @cscpath + ' /nologo /target:library /out:' +
                             '"' + @assempath + '" "' + @srcpath + '"'
PRINT @cmd
DECLARE @ret int
EXEC @ret = xp_cmdshell @cmd
IF @ret <> 0
BEGIN 
   RAISERROR('Compilation failed, check result set for details.', 16, 1)
   RETURN @ret
END

I cannot say that I'm proud of the use of xp_cmdshell, but I decided that for "food for thought" it was good enough and I discuss alternatives in the final section of the chapter. I set up the command to compile the file, and to facilitate troubleshooting, I print it out. We need to check the return status, since that is the only way to detect compilation errors. For the same reason it is essential not to use the no_output parameter to xp_cmdshell; if compilation fails, we do want to see the error messages.

Once compiled, we read the assembly from disk, using OPENROWSET(BULK) with the option SINGLE_BLOB:

DECLARE @sql nvarchar(MAX),
        @assem_bits varbinary(MAX)
SELECT @sql = 'SELECT @bits = A.bits
               FROM   OPENROWSET(BULK ' + quotename(@assempath, '''') + 
              ', SINGLE_BLOB) AS A(bits)'
PRINT @sql
EXEC sp_executesql @sql, N'@bits varbinary(MAX) OUTPUT', @assem_bits OUTPUT

We need to use dynamic SQL here since OPENROWSET(BULK) does not accept a parameter for the file name. (There is also a permission reason as explained in the chapter about bulk load in this appendix.) I use quotename to protect against SQL injection in the file name, although it has the side effect of negating the declaration of @assempath as nvarchar(250), since quotename returns NULL for input longer than 128 characters.

Next step is to compute the hash and add it as a trusted assembly:

DECLARE @hash varbinary(64) = hashbytes('SHA2_512', @assem_bits)
DECLARE @description nvarchar(4000) = quotename(db_name()) + '.' + 
                                      quotename(@assemname)
EXEC sp_add_trusted_assembly @hash, @description

When you enter a hash, you can optionally enter a description, so that you know what the hash is for. The recommendation from Microsoft is that we should use the canonical name which you can find in the column clr_name in sys.assemblies. I found it better to enter the name of the database and the plain name of the assembly, as this permits us to clean up old hashes as we will see later.

All this done, we can now create the assembly. Again, we need to use dynamic SQL to incorporate the values in the parameters @assemname and @permission_set. The @bits, however, can be passed as a parameter, as CREATE ASSEMBLY supports a variable for this.

SELECT @sql = CASE WHEN assemblyproperty(@assemname, 'CLRName') IS NULL
                   THEN 'CREATE'
                   ELSE 'ALTER'
               END + ' ASSEMBLY ' + quotename(@assemname) + ' FROM @bits ' +
                     'WITH PERMISSION_SET = ' + @permission_set
PRINT @sql
EXEC sp_executesql @sql, N'@bits varbinary(MAX)', @assem_bits

Since there is no CREATE OR ALTER ASSEMBLY, we need to check whether the assembly already exists. A little side note here: As you see, I use assemblyproperty for this check. I first tried to query sys.assemblies, but I found rather than resolving to the local database, it resolved to master.sys.assemblies. (As I have said, writing your own system procedures is unsupported and at your own risk!)

When you develop an assembly, and load it over and over again, this could result in a large number of hashes being stored. To avoid littering, we look up the description up in sys.trusted_assemblies and delete all hashes that matches the description except for the one we just created:

SELECT @sql = STRING_AGG('EXEC sp_drop_trusted_assembly ' + 
                          convert(varchar(200), hash, 1), char(13) + char(10))
FROM   sys.trusted_assemblies
WHERE  description = @description
  AND  hash <> @hash
IF @sql IS NOT NULL
BEGIN 
   PRINT @sql
   EXEC(@sql)
END

Normally, there would only be a single old hash, but there can be more than one, if CREATE ASSEMBLY has failed previously. Rather than running a cursor, we form an SQL string with the help of the new aggregate function string_agg, introduced in SQL 2017.

The last thing to do is to cater for the permissions. To do this properly, we should use the same technique as in GrantPermsToSP and GrantPermsToSP_server.sql, to wit, drop any existing certificate and create a new with a random password. However, to keep the demo script concise, I'm using a hard-coded password. (None of the two aforementioned fits in here as-is.)

CREATE CERTIFICATE sp_play_load_assembly$cert
   ENCRYPTION BY PASSWORD = 'We Can Work it Out'
   WITH SUBJECT = '"SIGN sp_play_load_assembly GRANT CONTROL SERVER"'
CREATE LOGIN sp_play_load_assembly$certlogin 
      FROM CERTIFICATE sp_play_load_assembly$cert
GRANT CONTROL SERVER TO sp_play_load_assembly$certlogin
go
ADD SIGNATURE TO sp_play_load_assembly 
    BY CERTIFICATE sp_play_load_assembly$cert
    WITH PASSWORD = 'We Can Work it Out'
go
GRANT EXECUTE ON sp_play_load_assembly TO public

We first create the certificate and the login and grant the login CONTROL SERVER, whereupon we sign the procedure. The final step is to grant EXECUTE permission on the procedure. Since we are in master, there is only one entity to grant permission to and that is public. This is alright, since the procedure has its own permission check.

Testing it Out

We have looked at sp_play_load_assembly. Now it is the time to test it. First, let's make sure that all configuration parameters are in shape. We need to have the CLR and xp_cmdshell enabled, and we should have CLR Strict Security in force:

EXEC sp_configure 'clr enabled', 1
EXEC sp_configure 'clr strict security', 1
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

As always, we need a database to play with:

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

The test pilot for this chapter is Mean Mr. Mustard who is a power user in AssemPlay, and who we trust to create safe assemblies, but not more.

CREATE LOGIN MrMustard WITH PASSWORD = 'Shaves in the dark trying to save paper'
ALTER SERVER ROLE SafeAssemblyLoaders ADD MEMBER MrMustard
go
USE AssemPlay
go
CREATE USER MrMustard
ALTER ROLE db_owner ADD MEMBER MrMustard

Mr. Mustard has written a simple C# procedure, which you also find in the file democlr.cs. All it does is to print a message.

using Microsoft.SqlServer.Server;

public partial class StoredProcedures {

   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void democlr()
   {
       SqlContext.Pipe.Send("She came in through the bathroom window");
   }
};

Mr. Mustard uses this file to test sp_play_load_assembly. He first loads the assembly, creates a stored procedure from the assembly and then runs the procedure. (You may have to change C:\temp to a path that works on your system. Keep in mind that the path is resolved from SQL Server and not your local machine.)

EXECUTE AS LOGIN = 'MrMustard'
go
EXEC sp_play_load_assembly 'C:\temp\democlr.cs', 'DemoClr', 'SAFE'
go
CREATE OR ALTER PROCEDURE DemoClr AS
   EXTERNAL NAME DemoClr.StoredProcedures.democlr
go
EXEC DemoClr
go
REVERT

The output:

Most of the output is debug: the first is the command line. The result set with the output column is the output from xp_cmdshell where no news is good news. Then follows the debug output from the two dynamic SQL statements. (If you don't want to see them every time, you can add a @debug parameter, so that they are only printed when @debug = 1, but don't remove the PRINT entirely – you should always have a debug PRINT when you work with dynamic SQL. You never know when you may need it.) The last line is from the successful execution of the DemoClr procedure.

Rerun the above, and you will see that the last lines are a little different:

That is, CREATE is now ALTER, and you can see a call to sp_drop_trusted_assembly to delete the hash from the first compilation.

To test that we handle compilation errors correctly, introduce a compilation error in the file C:\temp\democlr.cs. For instance, add an h before "SqlContext", and run

EXEC sp_play_load_assembly 'C:\Temp\democlr.cs', 'DemoClr', 'SAFE'

The output:

As you can see, the procedure prints the compilation error and aborts without trying to load the assembly. When you have completed this test, restore democlr.cs to its original contents.

Let's also test that the check for CREATE ASSEMBLY permission works. Look what happens when Mr. Mustard tries to create the assembly in master:

USE master
go
EXECUTE AS LOGIN = 'MrMustard'
EXEC sp_play_load_assembly 'C:\temp\democlr.cs', 'DemoClr', 'SAFE'
go
REVERT
USE AssemPlay

The output:

Let's move on. Mr. Mustard also has a small C# function he wants to try. The below is also in the file sunking.cs.

public class sun
{
    static long king = 4711;

    [Microsoft.SqlServer.Server.SqlFunction]
    public static long sunking()
    {
        king++;
        return (king);
    }
};

Seasoned writers of CLR modules immediately see that this a really bad class to put in an assembly because of the static field, but Mr. Mustard sees no problems with it, so he goes along:

EXECUTE AS LOGIN = 'MrMustard'
go
EXEC sp_play_load_assembly 'C:\temp\sunking.cs', 'SunKing', 'SAFE'
go
REVERT

However, SQL Server stops him at the gate:

Mr. Mustard gets the hint, and changes the permission set parameter to UNSAFE:

EXECUTE AS LOGIN = 'MrMustard'
go
EXEC sp_play_load_assembly 'C:\temp\sunking.cs', 'SunKing', 'UNSAFE'
go
REVERT

For good reasons, we only had faith in Mr. Mustard writing safe assemblies. There is a new error message:

But just for the sake of it, you can try loading this silly procedure as yourself, create an SQL function and run it a few times:

EXEC sp_play_load_assembly 'C:\temp\sunking.cs', 'SunKing', 'UNSAFE'
go
CREATE OR ALTER FUNCTION SunKing () RETURNS bigint AS
   EXTERNAL NAME SunKing.sun.sunking 
go
SELECT dbo.SunKing()
SELECT dbo.SunKing()
SELECT dbo.SunKing()

This is the output:

You may note that there is a call to sp_drop_trusted_assembly. This is the hash that was stored when Mr. Mustard attempted to create the assembly as safe.

Note: In case you do not understand why this assembly is bad: if the assembly is unloaded because of memory pressure, or SQL Server is restarted, the function will start over and return numbers from 4711 and up. So if you thought this could be used as your own number generator, just forget it.

To conclude our testing, we take a quick look at sys.trusted_assemblies.

SELECT * FROM sys.trusted_assemblies

We see something like this (I've have broken up the result set over two lines to fit within the page width):

As you see, beside the hash and the description there is only the time for the creation and who created it. We can note that the auditing is performed on original_login() rather than SYSTEM_USER. (In the latter case, we would have seen MrMustard on the line for DemoClr.

Concluding Remarks and Clean-up

Before we come to the clean-up, I want to make a few more comments about the solution.

One thing which is missing is support for availability groups. If you are deploying an assembly to a database that is an availability group, you need to load the hash on all servers in the AG. I did not go this far in sp_play_load_assembly, but this is left as an exercise for the reader. For guidance, you can look in the script GrantPermsToSP_server.sql, where I have implemented this.

As I said, I'm not overly proud of the use of xp_cmdshell. An alternative that I have not investigated but which may be possible is to write a CLR procedure that accesses the C# compiler through a class interface without going to the command line. That would also permit a better experience for the error messages than the result set from xp_cmdshell. I will have to admit that I don't know if there are classes to access the C# compiler directly. Even if there is, it may not be possible to use them from within SQL Server, since far from all .NET classes are permitted.

However, xp_cmdshell is only one of the problems, and in a way the smaller one. The bigger problem is the source path as such. As long as you are in development and have SQL Server locally on your workstation, it works very well. But when you are to deploy to production, who says that you will run the installation script directly from the SQL Server machine? Or that you are even lucky to run the script on a machine from where you can access a file share that SQL Server also can access?

One alternative is to have a parameter which permits you to pass the source code directly to the procedure. The hypothetical CLR procedure to call the C# compiler could pass the source code directly, and the compilation method could be nice to return the assembly bits as an output parameter (removing the need to use OPENROWSET(BULK). But even if all this would work, you would still have to write C# in an SQL file, which means that syntax highlighting and Intellisense will not be in force. You must also remember to double all single quotes in the C# code. Even if sp_play_load_assembly is mainly intended for short C# procedures and functions, the poor editing experience would be discouraging.

In the light of this, I think the best bet is to have something like a PowerShell script that compiles the source code, reads the DLL and sends the assembly bits to a stored procedure which performs the rest of the job. That is, the procedure computes and stores the hash, creates the assembly and cleans up old hashes.

And speaking of that, the automatic dropping of old hashes works well, as long as your set of databases is well-defined. But if you restore copies of databases, the scheme can break down a little bit. Say that you restore a copy of AssemPlay as AssemPlayTest. If you go and change DemoClr in AssemPlayTest, there is no issue, but if you change DemoClr in AssemPlay, this will pull the rug for the assembly in AssemPlayTest, because the procedure assumes that AssemPlay is the only database to use the assembly and therefore delete the old hash that still in use in AssemPlayTest. If you think you will do this often, you make want to take out the clean-up part or make it optional.

Here is a clean-up script for this chapter. Note that the script disables xp_cmdshell, but keeps the CLR and CLR Strict Security enabled. The script drops the hashes we created for the DemoClr and SunKing assemblies.

USE master
go
ALTER DATABASE AssemPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE AssemPlay
go
DROP LOGIN AssemPlay$owner
go
DROP PROCEDURE sp_play_load_assembly
go
DROP PROCEDURE sp_play_check_permission
go
DROP LOGIN MrMustard
go
DROP SERVER ROLE UnsafeAssemblyLoaders
go
DROP SERVER ROLE ExtAccessAssemblyLoaders
go
DROP SERVER ROLE SafeAssemblyLoaders
go
DROP LOGIN sp_play_load_assembly$certlogin
go
DROP CERTIFICATE sp_play_load_assembly$cert
go
DECLARE @sql nvarchar(MAX)
SELECT @sql = STRING_AGG('EXEC sp_drop_trusted_assembly ' + 
                           convert(varchar(200), hash, 1), char(13) + char(10))
FROM   sys.trusted_assemblies
WHERE  description LIKE '%AssemPlay%'
PRINT @sql
EXEC(@sql)
go
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

Revision History

2021-10-30

The same change as in the main article. That is, subjects for certificates are now enclosed in double quotes.

2021-01-16

Changed the demo script for the chapter Letting Users Start Specific Jobs, so that the job is now owned by an application login to align with what I say in my new article Don't Let Your Permissions Be Hijacked!

2020-11-05

Added a section about an alternate solution in the chapter on linked servers that packages the AUTHENTICATE SERVER permission in a function.

2019-12-08

Some editorial changes to the chapter on bulk load and the section First Solution: Using Certificates Only as a consquence of the changes to the server-level recipe in the main article. Added a note in the CLR chapter to explain why the assembly sunking is bad.

2017-12-03

First version of this appendix.

Back to my home page.