SQL SERVER 2005开始提供对存储过程签名

SQLSERVER 2005 开始 提供的对存储过程签名 (signature) 是我非常喜欢的。 如果我们要编写一个存储过程 , 执行该存储过程里的代码需要权限 P, 并且我们想要用户 Alice 可以执行这个存储过程，但是我们不想将权限 P 直接赋予给用户 Alice ， 我们可以用证书 (

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