Have you ever had the need to give elevated permissions via a stored procedure above that what the user calling the procedure might have?

Perhaps you’ve got a table in your database that contains top secret information and you only want that information to be accessed by via a proc, you’re users are denied access to the underlying table.

We really don’t want our user ‘MoneyPenny’ to be able to access that top secret table in our database so we’ll deny her access to it.

DENY SELECT ON spys TO MoneyPenny

So now if she ever ties to access that table, she’ll just get the message,

What happens if we need MoneyPenny to be able to access a spy’s code name?

For whatever reason, the need has come up for MoneyPenny to be able to match up a spy’s id with their code name.  But as we need to make sure that she can only ever see their code name and never their real name, we’ll create a stored procedure for her to use.

CREATE PROC ID2CodeName (@ID INT)
AS
BEGIN
SELECT CodeName
FROM Spys
WHERE ID = @ID
END

and grant execute to MoneyPenny

GRANT EXECUTE ON ID2CodeName TO MoneyPenny

Looks simple enough but whenever she tries to run it, she’s getting the error…

Signed Stored Procedures

One way that we can do this is by signing the procedure (you can also use this with functions and triggers) with a certificate or an asymmetric key.

In this post I’m only going to look into signing a stored procedure with a certificate but the same ideas can be applied for the other objects and with an asymmetric key.

So…

We’re going to create a certificate and sign our stored proc using that certificate.  We’ll then create a user based on the certificate and grant the new certificate user the appropriate permissions to run the stored procedure.

Once that’s done, when we run the procedure, we’ll be using the permission set of the newly created user.

Create a Certificate

CREATE CERTIFICATE ID2CodeName_cert
ENCRYPTION BY PASSWORD = 'P455w0rd'
WITH SUBJECT = 'Certificate to sign ID2CodeName Procedure'

If you now check out sys.certificates, you should see your newly created certificate.

 SELECT * FROM sys.certificates

Sign the Stored Procedure

Now that the certificate has been created, we can use it to sign our procedure.  You’ll need to use the password that you used to encrypt the certificate so make sure you keep that nice and safe.

ADD SIGNATURE TO [dbo].[ID2CodeName]
BY CERTIFICATE ID2CodeName_cert WITH PASSWORD = 'P455w0rd'

You can see all the objects that have been signed by running the following code,

SELECT OBJECT_NAME(crypt_properties.major_id) AS ObjectName,
 certificates.name AS CertificateName,
 crypt_properties.crypt_type_desc
FROM sys.crypt_properties
JOIN sys.certificates ON crypt_properties.thumbprint = certificates.thumbprint
WHERE crypt_type_desc = 'SIGNATURE BY CERTIFICATE'

Running that, we should see the proc that we’ve just signed.

Create Certificate User

The next step that we need to do is to create a certificate user…

CREATE USER CertUser FROM CERTIFICATE ID2CodeName_cert

… and grant that user all the nesassary permissions to run the procedure (in our case, that’s SELECT on the spy table).

GRANT SELECT ON spys TO CertUser

Test It Out!

Now that we’ve set all that up, whenever the ID2CodeName procedure is called, it’ll run using the permissions of the CertUser (even with it’s running as EXECUTE AS CALLER).  Lets see what happens when MoneyPenny tries to execute the proc now…

EXEC ID2CodeName 2

And there we have it!

One Thing To Note:
If You Change The Proc It’ll Need to Be Resigned

That’s because the digital signature is really just the query hash, encrypted using the certificate.

If the procedure changes, the query hash will also change.  That means that it’ll no longer match the signature and the signature will be invalid.

If this happens the permissions will revert back to the caller’s permissions.

What If The Procedure Accesses Multiple Databases?

In this case, you’ll need to make sure that the certificate is imported into every database that you need to access.  Once it’s there you can create a user tied to that certificate in that same way that we did above.