SQL_Elvis (6/21/2010)
One benefit is that the Certificate and certificate user account are managed outside of the stored procedure code and can be updated or the user account changed without having to change the stored proc code for every procedure that uses thecert. "With Execute as" is embedded in the code so each proc would have to be altered in the case that you needed to change the user (ex. user account is compromised by a terminated employee).
I think you get the same benefit by using "proxy" logins, that can't actually log-in to the DB, so the login itself can't be compromised:
CREATE USER [Proxy_User] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
Then you just assign the SP to EXECUTE AS that login:
CREATE PROCEDURE [dbo].[usp_xxx] (@SQL VARCHAR(MAX))
WITH EXECUTE AS N'Proxy_User'
But I don't see how either using the certificates or the proxy login protects you from SQL injection attacks when using dynamic SQL, as the SP can still do anything that the certificate user/proxy login has permissions to do. What it does do is make it so we don't have to give end-users direct access to the data, or have the application login with an account that can be compromised.