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).

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).

CREATE USER [Proxy_User] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

CREATE PROCEDURE [dbo].[usp_xxx] (@SQL VARCHAR(MAX))
  WITH EXECUTE AS N'Proxy_User'

UMG Developer (6/21/2010)

---

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.

janyalee (6/21/2010)

---

Using a Certificate Versus Stored Procedure WITH EXECUTE AS
Hello Jonathan, I appreciated this article. It got me wondering, what signing with a certificate gets us that SP WITH EXECUTE AS functionality does not?
Either way we grant myCertUserName read access to the underlying tables called by the sp.
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\myCertUserName'
AS
SELECT user_name();
GO
Thank you in advance for your feedback.

Jonathan AC Roberts (6/21/2010)
Both methods achieve the same thing but using certificates is generally recommended. Some things are different for example if there is auditing a table with a trigger and the function user_name() is used then the function would return the EXECUTE AS user not the actual caller of the stored procedure. Also, if another stored procedure is called from within the stored procedure it would execute that stored procedure with the EXECUTE AS user rather than the actual user.

Jonathan AC Roberts (6/21/2010)

---

You need to grant EXECUTE permission to the stored procedure to all users who need to execute the procedure but you do not need to grant them any access to the tables if you use a certificate.

The procedure is executed by user 'xyz' but the stored procedure gets its permission to select from the table through the certificate.