Well, it has been a couple of weeks since my last blog post and over a month since Maintaining Security and Performance Using Stored Procedures Part I – Using EXECUTE AS was posted, although I did spend time working on the originally unplanned follow up to that post when I would have been doing this post.  I know you all have been anxiously awaiting this post.

EXECUTE AS vs. Signing

Like EXECUTE AS, the ability to sign a module (stored procedure, DML trigger, function, or assembly) with a certificate was added in SQL Server 2005.  In addition to being able to allow access to objects within the current database context, signing also allows you to access resources in another database or that require server level permissions.  With EXECUTE AS on functions, stored procedures, and DML triggers you are limited by database context, so if you need access to objects in another database you are out of luck, even if the EXECUTE AS user has proper rights in the database.  You can download code that demonstrates this behavior here.

Demonstrations of Signing

Laurentiu Cristofor has an excellent blog post that demonstrates signing a stored procedure to grant server level permissions here, so I am not going to duplicate his work in this post.  I will demonstrate how to sign a procedure for use within a database and when accessing another database.

Using Signing to enable Dynamic SQL within the database

USE AdventureWorks;
GO
/*
Create a restricted_login
*/CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';
GO
/*
Create a Certificate first
*/    
CREATE CERTIFICATE cert_dynamic_sql 
ENCRYPTION BY PASSWORD = 'c3rtificatePa$$w0rd'
WITH subject = 'Dynamic SQL Security'   
GO 
/* 
Create user based on the certificate
*/ 
CREATE USER certificate_user FROM CERTIFICATE cert_dynamic_sql;
/*
Give the certificate_user select on all objects
*/GRANT SELECT ON SCHEMA::Person TO certificate_user;
GO
/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;
GO
/*
Create the procedure
*/CREATE PROCEDURE dbo.FindPhoneByName
(
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null
)
AS   
BEGIN
 SET NOCOUNT ON;
Declare @sql_cmd nvarchar(2000),
@select nvarchar(1000),
@where nvarchar(1000),
@parameters nvarchar(1000);
 Set @parameters = N'@FirstName nvarchar(50), @LastName nvarchar(50)';
Set @select = N'Select
Title,
FirstName,
MiddleName,
LastName,
Suffix,
Phone  
From
Person.Contact';
Set @where = N' Where 1=1 '                
If @LastName is not null
Begin
Set @where = @where + N' And LastName Like @LastName + N''%'' ';
End;
If @FirstName is not null     
Begin
Set @where = @where + N' And FirstName Like @FirstName + N''%''';
End;
Set @sql_cmd = @select + @where;
Exec sys.sp_executesql @sql_cmd, @parameters, @LastName = @LastName, @FirstName = @FirstName;
Return;         
END
GO
/*
Give the restricted user exec on the proceduere
*/GRANT EXEC ON dbo.FindPhoneByName TO restricted_user; 
GO
/*
Change context to the restricted rights user
*/EXECUTE AS LOGIN = 'restricted_login';
GO
/*
Exec the procedure - will fail on the dynamic portion
*/EXEC [dbo].FindPhoneByName;
GO
/* 
Return to sysadmin rights
*/revert;
GO
/*
Sign the procedure
*/ADD SIGNATURE TO [dbo].[FindPhoneByName]
BY CERTIFICATE cert_dynamic_sql
WITH PASSWORD = 'c3rtificatePa$$w0rd';
GO
/*
Change context to the restricted rights user
*/EXECUTE AS LOGIN = 'restricted_login';
GO
/*
Exec the procedure - will work now
*/EXEC [dbo].FindPhoneByName;
GO
/* 
Return to sysadmin rights
*/revert;
GO

Using Signing to Access an Object in Another Database

USE MASTER;
GO
/*
Create the restricted_login
*/CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';
GO
USE Northwind;
GO
/*
Create the Certificate
*/    
CREATE CERTIFICATE cert_access_other_db 
ENCRYPTION BY PASSWORD = 'c3rtPa$$word'
WITH subject = 'Access Other DB'   
GO 
/* 
Backup the certificate being sure to use a Private Key
*/BACKUP CERTIFICATE cert_access_other_db TO FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk' ,
ENCRYPTION BY PASSWORD = '3ncRyptKeyPa$$word',
DECRYPTION BY PASSWORD = 'c3rtPa$$word');
GO
/*
Create the certificate user in the Northwind and give needed permissions
*/CREATE USER certificate_user FROM CERTIFICATE cert_access_other_db;
GO
GRANT SELECT ON dbo.Categories TO certificate_user;
GO 
USE AdventureWorks;
GO
/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;
GO
/*
Create the procedure
*/CREATE PROCEDURE [dbo].access_other_db
AS
SET NOCOUNT ON
SELECT 
SYSTEM_USER AS USERName, 
*
FROM
[Northwind].dbo.[Categories] AS C;
RETURN;    
GO
/*
Give the restricted_user execute rights on the sp
*/GRANT EXEC ON dbo.access_other_db TO restricted_user;
GO
/*
Create the certificate in this database from the file
*/CREATE CERTIFICATE cert_access_other_db FROM FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk',
DECRYPTION BY PASSWORD = '3ncRyptKeyPa$$word', /*The password used to create the private key */ENCRYPTION BY PASSWORD = 'D3cryptKeyPa$$word');
GO
/* 
Execute as a sysadmin - works - this is my user
*/EXEC [dbo].[access_other_db];
GO
/*
Now execute as the restricted user 
*/EXECUTE AS LOGIN = 'restricted_login';
GO
/*
This will fail.
*/EXEC [dbo].[access_other_db];
GO
/*
Back to the sysadmin level
*/Revert;
GO
/*
Sign the procedure 
*/ADD SIGNATURE TO dbo.access_other_db
BY CERTIFICATE cert_access_other_db WITH Password = 'D3cryptKeyPa$$word'
GO
/*
Now execute as the restricted user 
*/EXECUTE AS LOGIN = 'restricted_login';
GO
/*
This will now work.
*/EXEC [dbo].[access_other_db];
GO
/*
Back to the sysadmin level
*/Revert;
GO
/*
Be sure to delete the certificate and
private key when done.
*/

Summary

As you can see from this post and the previous post (or two), the SQL Server team has given you some good options when it comes to using Stored Procedures for data access and manipulation while still maintaining security.  You can use EXECUTE AS to allow cross-schema or within database access and you can use module signing to allow access to system objects or cross-database queries without specifically granting users access to the objects.

Resources

After having planned and started the post because I had not found anything outside of Books On Line, I found a few resources that covered the material as well, and I used each to help me write this post once I found them.

• I linked to Laurentiu Cristofor’s post earlier
• Erland Sommarskog has an excellent write-up on Giving Permissions through Stored Procedures which handles this subject very thoroughly as you would expect form Erland.  Erland’s post helped me get past the fact that I needed to use a private key in order to get the cross database piece working as none of my other resources did this.
• Jonathan Kehayias also answered this this forum post with an example.

In reality I could have just posted these links as these other folks covered the subject on signing thoroughly, but I decided that since I did the research and wanted to try the code myself that I’d share my experiences as well, crediting these guys for their work and hopefully sending them some traffic, however limited, from this blog.

Finally all the MY code from this post can be downloaded from here.