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)
as
   declare @sqlcmd varchar(2000);

 

   begin tran;

 

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

   begin

      rollback tran;
      print 'Cannot create login'
      return;
   end

 

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

 

   commit tran;
go

 

-- 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@))%';
revert;

 

-- 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@))%';
revert;

 

-- 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