Introduction:
In this article,i am going to explain about how to encrypt a stored procedure and how to set a stored
procedure security while using exec statement.

Main:
Just like a view, stored procedure Transact-SQL definitions can have their contents encrypted in the database,
removing the ability to read the procedure’s definition.

In order to encrypt the stored procedure, WITH ENCRYPTION is designated after the name of the new stored procedure,
as this next example demonstrates:

CREATE PROCEDURE dbo.usp_SEL_NetPayHistory WITH ENCRYPTION AS   SELECT NetID, RateChangeDate, Rate, PayFrequency, ModifiedDate FROM HumanResources.NetPayHistory   GO

Once you’ve created WITH ENCRYPTION, you’ll be unable to view the procedure’s text definition:

– View the procedure’s text
EXEC sp_helptext usp_SEL_NetPayHistory

The text for object ‘usp_SEL_NetPayHistory’ is encrypted.

Encryption can be defined using either CREATE PROCEDURE or ALTER PROCEDURE, but be sure to save your source code, as
the existing encrypted text cannot be decrypted easily.

Using EXECUTE AS to Specify the Procedure’s Security Context

The WITH EXECUTE AS clause allows you to specify the security context that a stored procedure executes under,
overriding the default security of the stored procedure caller. In this case, security context refers to the
permissions of the user executing the stored procedure.

You have the option to execute a stored procedure under

* The security context of the caller
* The person who authored or last altered the procedure
* A specific login (if you have IMPERSONATE permissions for that person’s login)
* The owner of the stored procedure

CREATE USER James GO GRANT EXEC ON usp_DEL_EmployeeSalary to James   CREATE USER James   GRANT SELECT ON OBJECT::HumanResources.Employee TO James GO

Conclusion:
Hope this helps,
Happy coding.