[历程]
历程一共分为2个部分
*/
--==================(I)示例筹办=====================
--1.)创立数据库主密钥
USENorthwind
GO
CREATEMASTERKEYENCRYPTIONBYPASSWORD=P@ssw0rd
--2.)创立签订存储历程所必要的证书
CREATECERTIFICATEcert_Products
WITHSUBJECT=ProductsSign,
START_DATE=2006/1/1,
EXPIRY_DATE=2008/1/1
--3.)创立SPDeveloper登录帐户和用户，该用户创立会见Products表的存储历程
CREATELOGIN[SPDeveloper]WITHPASSWORD=NP@ssw0rd,DEFAULT_DATABASE=[Northwind]
GO
CREATEUSER[SPDeveloper]FORLOGINSPDeveloperWITHDEFAULT_SCHEMA=[SPDeveloper]
GO
CREATESCHEMAproductsAUTHORIZATIONSPDeveloper
GO
EXECsp_addrolemember@rolename=db_owner,@membername=SPDeveloper
--4.)以SPDeveloper的身份创立存储历程products.usp_Products
EXECUTEASUSER=SPDeveloper
GO
CREATEPROCEDUREproducts.usp_Products
AS
SELECTTOP5*FROMdbo.Products
GO
REVERT
SELECTUSER
--4.)创立一般用户Jerry
CREATELOGINjerryWITHPASSWORD=NP@ssw0rd,DEFAULT_DATABASE=[Northwind]
CREATEUSERjerryFORLOGINjerry
--==================(II)利用证书签订存储历程=====================
--1.)授与用户Jerry实行存储历程的权限
GRANTEXECUTEONproducts.usp_ProductsTOjerry
--2.)以Jerry的身份实行存储历程失利，由于具有全链是断裂的
EXECUTEASUSER=jerry
SELECTUSER
GO
EXECUTEproducts.usp_Products
GO
REVERT
--3.)利用证书在以后数据库创立用户ProductsReader，
--并为该用户付与读取Products表的权限
CREATEUSERProductsReaderFORCERTIFICATEcert_Products
GO
GRANTSELECTONProductsTOProductsReader
--4.)利用证书签订以后存储历程
ADDSIGNATURETOproducts.usp_ProductsBYCERTIFICATEcert_Products
--4.)以Jerry的身份从头实行存储历程，乐成，
--由于存储历程将以ProductsReader的权限高低文实行
EXECUTEASUSER=jerry
SELECTUSER
GO
EXECUTEproducts.usp_Products