SQLSERVER 2005提供的对存储过程签名(signature)功能是我最喜欢的。
  
  
  
  如果我们要编写一个存储过程,执行该存储过程里的代码需要权限P,并且我们想要用户Alice可以执行这个存储过程，但是我们不想将权限P直接赋予给用户Alice， 我们可以用证书(certificate)对这个存储过程进行签名来完成这一需求：
  
  
  
  a) 如果P是一个数据库级别的权限，那我们可以在相应的数据库中创建一个证书，使用证书创建一个用户(user)，然后将权限p授权给这个用户
  
  b) 如果P是一个服务器级别的权限，那我们能要在master数据库中创建一个证书，使用证书创建一个登录(login)，然后将权限P授权给这个登录
  
  
  
   签名之后，存储过程就会在执行期间获得权限P，而我们仅仅授予了Alice执行这个存储过程的权限。
  
  
  
   如果我们既需要服务器级别的权限，又需要数据库级别的权限，那么我们既要创建用户，又要创建登录。下面列出步骤：
  
  
  
  1) 在数据库中创建证书
  
  2) 创建一个用户(user)并映射到这个证书
  
  3) 将数据库级别的权限授予这个用户
  
  4) 备份这个证书
  
  5) 在master数据库中还原这个证书
  
  6) 创建一个登录(login)，并将登录映射到证书
  
  7) 将服务器级别的权限授予给这个登录
  
  
  
   我们也可以先在master数据库中创建证书，然后再将其还原到用户alice工作的数据库。也就是证书的创建顺序并不重要，重要的是master数据库中的证书一定要和用户数据库中的相同。
  
  
  
  下面是演示：
  
  
  
  
  
  
  -- 目的
  -- 展示如何用证书签名一个存储过程,
  --并授予证书相应的权限
  
  
  
  create database demo;
  
  use demo;
  
  
  
  -- 创建一个存储过程，该过程会创建一个主体（包含登录和用户）
  -- 这需要服务器级别的ALTER ANY LOGIN 权限
  -- 和数据库级别的 ALTER ANY USER 权限
  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
  
  
  
  -- 调用这个存储过程
  -- 创建主体
  sp_CreatePrincipal 'alice', 'Apufe@))%';
  
  
  
  --我们需要让alice可以调用这个存储过程，创建新的主体，
  -- 但并不直接授予她权限（创建主体的权限，译者注）
  grant execute on sp_CreatePrincipal to alice;
  
  
  
  -- 目前 alice还不能创建主体
  execute as login = 'alice';
  sp_CreatePrincipal 'bob', 'Apufe@))%';
  revert;
  
  
  
  -- 使用证书对存储过程进行签名
  -- 首先我们要创建一个数据库主密钥（database master key）
  create master key encryption by password = 'Apufe@))%';
  create certificate certSignCreatePrincipal with subject = 'for signing procedure sp_CreatePrincipal';
  
  
  
  -- 签名存储过程sp_CreatePrincipal
  add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
  
  -- 现在签名完成了，可以将证书的私钥移除了
  alter certificate certSignCreatePrincipal remove private key;
  
  -- 对证书进行备份，随后在master数据库中将要使用该备份
  backup certificate certSignCreatePrincipal to file = 'certSignCreatePrincipal.cer';
  
  
  
  -- 创建一个用户并将用户映射到证书
  create user u_certSignCreatePrincipal from certificate certSignCreatePrincipal;
  --通过授权映射映射的方式将ALTER ANY USER权限赋给证书 （因为用户和证书是映射的，所以权限也就赋给了证书，SQLSERVER本身没有直接将权限赋给证书的方法。译者注）
  grant alter any user to u_certSignCreatePrincipal;
  
  
  
  -- 在master数据库中创建该证书
  use master;
  create certificate certSignCreatePrincipal from file = 'certSignCreatePrincipal.cer';
  
  -- 创建登录并映射到证书
  create login l_certSignCreatePrincipal from certificate certSignCreatePrincipal;
  -- 通过授权映射登录的方式将ALTER ANY LOGIN权限赋给证书
  grant alter any login to l_certSignCreatePrincipal;
  
  
  
  -- 完成！
  use demo;
  
  
  
  -- 验证一下，master数据库中的证书和demo数据库中的证书是一样的。
  select c.name from sys.certificates c, master.sys.certificates mc where c.thumbprint = mc.thumbprint;
  
  
  
  -- 现在alice可以创建主体了
  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