SQL Server 2005: procedure signing demo

The ability to sign procedures is my favorite cryptographic feature in SQL Server 2005.


If we want to write a procedure that requires permission P, and we want Alice to be able to execute the procedure but we do not want to grant her the permission P, we can sign the procedure with a certificate and:


a) if P is a database level permission, then we can create a certificate in the database, create a user mapped to it, and grant P to that user.

b) if P is a server level permission, then we can create a certificate in the master database, create a login mapped to it, and grant P to that login.


By doing this, because of its signature, the procedure will gain P for the duration of its execution and we can just grant Alice permission to execute the procedure.


If we need to grant both server and database level permissions to a certificate, then we will have to create both a user and a login. The steps are as follows:


1) create the certificate in the database

2) create a user mapped to the certificate

3) grant db-level permissions to the user

4) backup certificate to a file

5) create the certificate in the master database from the file

6) create a login mapped to the certificate

7) grant server-level permissions to login


We can also first create the certificate in the master database and then recreate it in the user database where alice will work. The order in which we create the certificates does not matter, what matters is that the certificates are the same in both master and the user database.


And here is a demo:


-- Goals of this demo:
-- Show how a procedure can be signed by a certificate
-- and how to grant permissions to that certificate


create database demo;

use demo;


-- create a procedure that creates a new principal (login and user)
-- This requires ALTER ANY LOGIN at server level
-- and ALTER ANY USER at database level
create procedure sp_CreatePrincipal
      @name varchar(256),
      @password varchar(128)
   declare @sqlcmd varchar(2000);


   begin tran;


   -- create login
   set @sqlcmd = 'create login ' + quotename(@name) + ' with password = ' + quotename(@password, '''');
   exec (@sqlcmd);
   if @@error <> 0


      rollback tran;
      print 'Cannot create login'


   -- create user
   set @sqlcmd = 'create user ' + quotename(@name);
   exec (@sqlcmd);
   if @@error <> 0
      rollback tran;
      print 'Cannot create user'


   commit tran;


-- now use this newly added procedure
-- to create a low privileged principal
sp_CreatePrincipal 'alice', 'Apufe@))%';


-- we'll now want alice to be able to use the procedure and create new principals
-- but without granting her directly the permissions
grant execute on sp_CreatePrincipal to alice;


-- verify that alice cannot create principals
execute as login = 'alice';
sp_CreatePrincipal 'bob', 'Apufe@))%';


-- create a certificate to sign the procedure
-- first, we'll need to create a database master key
create master key encryption by password = 'Apufe@))%';
create certificate certSignCreatePrincipal with subject = 'for signing procedure sp_CreatePrincipal';


-- sign procedure sp_CreatePrincipal
add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;

-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;

-- backup certificate to file; it will be used later to put the certificate in master
backup certificate certSignCreatePrincipal to file = 'certSignCreatePrincipal.cer';


-- create and map a user to the certificate
create user u_certSignCreatePrincipal from certificate certSignCreatePrincipal;
-- grant ALTER ANY USER to the certificate by granting it to the mapped user
grant alter any user to u_certSignCreatePrincipal;


-- create the same certificate in master now
use master;
create certificate certSignCreatePrincipal from file = 'certSignCreatePrincipal.cer';

-- create and map a login to the certificate
create login l_certSignCreatePrincipal from certificate certSignCreatePrincipal;
-- grant ALTER ANY LOGIN to the certificate by granting it to the mapped login
grant alter any login to l_certSignCreatePrincipal;


-- we're done!
use demo;


-- check that the certificate in demo matches the one in master
select c.name from sys.certificates c, master.sys.certificates mc where c.thumbprint = mc.thumbprint;


-- verify that alice can now create principals
execute as login = 'alice';
sp_CreatePrincipal 'bob', 'Apufe@))%';


-- cleanup
drop user u_certSignCreatePrincipal;
drop login l_certSignCreatePrincipal;
drop procedure sp_CreatePrincipal;
drop certificate certSignCreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;


use master;

drop certificate certSignCreatePrincipal;
drop database demo;
-- EOD

Comments (8)

  1. Chrisr says:

    I think I am missing something when it comes to providing data security. The above example is really nice when permission P is not SELECTUPDATEINSERTDELETE for a given table. However, I can prevent a user from directly accessing data on a table by implementing a stored procedure and only granting execute privs to the user on the procedure. This works if the procedure is signed or un-signed. I can also see how it would be beneficial if ownership chaining is involved with the stored procedure to be executed. But I don’t see the advantage of signing a procedure when that procedure operates against tables in its own schema and I wish to prevent granting direct privs to a user on the tables.

  2. You’re right, if what you want to do can be achieved by ownership chaining, then you don’t need to sign your procedure. However, the ability to sign procedures allows scenarios that are not possible with ownership chaining.

  3. brian says:

    Hi, I am missing the connection between Alice and u_certSignCreatePrincipal. Do all users that have EXECUTE have permissions to run the proc or just Alice?

  4. alice is just a low-privileged principal that is used to demo the fact that she can create logins via the proc, without explicitly having been granted the permission to create logins.

    u_certSignCreatePrincipal is used to grant database permissions to the certificate. l_certSignCreatePrincipal is used to grant server permissions to the certificate. Both these principals are mapped to the certificate via the FROM CERTIFICATE clause.

    In the example, alice is explicitly granted EXECUTE permission on the procedure.

  5. toddsriley says:

    Great article.  I am looking into using certificates and procedure signing as a temporary means of getting around granting direct permissions to the sp_OA procedures to our developers.  Eventually, we will, of course, move to using CLR procedures instead of sp_OA but as it stands, I just need a way to give these permissions to our developers through stored procedure signing.  

    Here’s what I have been able to do and where I am running into issues.  I started by creating a stored procedure that was similar to the situation we have in our real production environment.  The procedure simply uses the sp_send_dbmail stored procedure to send me an email:

    USE [my_database]

    CREATE   PROCEDURE dbo.P_Send_Mail_Test



    EXECUTE msdb.dbo.sp_send_dbmail

      @profile_name = ‘Name’,

       @recipients = ‘myemail@whatever.com’,

       @body = ‘Will this certification test work?’,

       @subject = ‘Certification Test’


    Then I execute the proc to ensure that it works.  It does.  Then I grant permissions to a user in our database who is not a member of the sysadmin server role, impersonate him, then try to execute the procedure – “you do not have permissions to execute sp_send_dbmail”.  This was expected.  So I reverted to my own permissions and went to the msdb database.  I created a master key that was encrypted by a password and then a certificate:


      WITH SUBJECT = ‘User Certificate to Extend Impersonation’,

      EXPIRY_DATE = ’12/31/2009′;

    Then I added a signature to sp_send_dbmail by the certificate I had created and backed the cert up to a file location. (I had also removed the private key before backing up a few of the times I tried this).  I created a user from the cert in msdb, granted execute permissions on sp_send_dbmail to the user and also granted authenticate to the user.

    I went back to my_database and created cert with same name from file location:

    CREATE CERTIFICATE My_Certificate FROM FILE = ‘C:cert_sign.cer’

    HERE IS WHERE I AM HAVING MY PROBLEM.  When I then try to sign my procedure, P_Send_Mail_Test, I get an error:

    ADD SIGNATURE TO P_Send_Mail_Test BY CERTIFICATE My_Certificate

    Msg 15556, Level 16, State 1, Line 2

    Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

    In other articles, I have seen examples where the author makes use of a private key and not the master key.  I would be more than happy to try this but do not know how to create a private key.  I even tried, when signing the procedure, using the signature from crypt_properties – didn’t work.  And also tried providing the password I used when creating the master key:

    ADD SIGNATURE TO P_Send_Mail_Test BY CERTIFICATE User_Certificate

    WITH  PASSWORD = ‘WhateverItWas’

    Without being able to sign my procedure, I don’t think this will work as P_Send_Mail_Test calls another procedure (namely sp_send_dbmail) from within it.  

    Any information you might be able to give me would be most helpful.



  6. A private key is created whenever you create a certificate, but it has to be explicitly backed up (it’s backed up separately from the certificate/public key). In my example, I removed the private key after signing the procedure with it, because I didn’t need it for any other signature.

    In your example, you would want to grant permissions to the P_Send_Mail_Test code, so you should sign this procedure, not sp_send_dbmail.

    But the issue is a bit more complex, because what you are trying to do is to enable cross database access via signatures. That is a more complex example of signature use than what I showed in this post. For cross-database access, I have an example here: http://blogs.msdn.com/lcris/archive/2006/10/24/sql-server-2005-demo-for-enabling-database-impersonation-for-cross-database-access.aspx. I used this example in the PASS presentation on execution context that you can find here: http://blogs.msdn.com/lcris/archive/2006/12/08/sql-server-2005-security-presentations-at-pass-pre-conference.aspx. Have a look at these resources – there is also a very good BOL article on this topic that I also reference in the presentation: http://msdn.microsoft.com/en-us/library/ms188304.aspx.

  7. Dermot O'Logical says:

    Time passes…. (yet I am still using SQL 2005)

    What do you have to do when you alter the stored procedure? I've tried to re-sign my procedure with the "add signature" step but I get the message:

    Msg 15556, Level 16, State 1, Line 1

    Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

    Hope you can help.



  8. Yes, you need to re-add the signature if you modify the stored procedure. Changing the procedure will invalidate the signatures attached to it.

    As for the error that you are getting, the error message indicates it has to do with lacking access to the certificate private key. The signing is done with the private key. If this certificate was created by someone else, you need to ask them to resign your procedure. If you created this certificate, then you might have dropped its private key as I did in the example – just load it from a backup, resign the procedure, then remove it again (or just leave it around if you are going to need it again). See msdn.microsoft.com/…/ms189511.aspx for how to load the private key.

Skip to main content