Code Module Signing in SQL Server 2005

SQL Server 2005 is that it provides the ability to digitally sign code modules to SP,triggers,functions with digital certificate. we sign the code with the private key contained within the certificate which means access to the tables are only provisioned to the certificates that have been used to sign the module. it is true for SPs too.

If it has an unbroken ownership chain, you carefully control which users get EXECUTE permission on the procedure, and you deny direct access to the underlying tables. But this doesn’t help in situations such as when the procedure has a broken ownership chain or executes dynamic SQL, requiring that the user executing the procedure have permissions to the underlying tables. Another way to achieve the same effect is to use EXECUTE AS, but this changes the security context under which the procedure executes. This may not be desirable, for example, if you need to record in the table the user who actually caused the procedure to run (short of requiring a user name as a parameter to the procedure).
Signing code modules has the additional benefit of protecting against unauthorized changes to the code module. Like other documents that are digitally signed, the certificate is invalidated when the code changes. The code doesn’t execute under the context of the certificate, so any objects that have their access provisioned to the certificate will not be accessible.
To do this, you create a certificate, associate it with a new user, and sign the procedure with the certificate. Grant this user whatever permissions are necessary to execute the stored procedure. In essence, this user is added to the security context of the stored procedure as a secondary identity. Then grant execute permissions to whatever users or roles need to execute the procedure. The following code shows these steps. Assume that you want to sign the mySchema.GetSecretStuff procedure, and that all of the referenced objects already exist in the database:

CREATE CERTIFICATE certCodeSign
ENCRYPTION BY PASSWORD = 'asdf@#$kdkf"
WITH SUBJECT = 'Codesigncertificate'
GO

-- Sign the stored procedure
ADD SIGNATURE TO mySchema.GetSecretStuff BY CERTIFICATE certCodeSign
WITH PASSWORD = 'asdf@#$kdkf'
GO

-- Map a user to the certificate
CREATE USER certUser FOR CERTIFICATE certCodeSign
GO

--Assign SELECT permissions to new certUser
GRANT SELECT ON SocialSecurity TO certUser
GO

-- Grant execute permission to the user who will run the code
GRANT EXECUTE ON mySchema.GetSecretStuff TO ProcedureUser
GO

Now only users explicitly granted EXECUTE permission on the stored procedure are able to access the table’s data.