Home Forums Article Discussions Article Discussions by Author Discuss content posted by Jonathan Roberts Using Certificates to Sign Stored Procedures RE: Using Certificates to Sign Stored Procedures

  • June 21, 2010 at 6:03 pm

    #1183043

    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.

    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.