Often developers require hiding the stored procedures from end users. In such case, developers can perform a one way encryption using the WITH ENCRYPTION clause. Once the definition of the stored procedure is encrypted, it cannot be decrypted or viewed by anyone.

Sample Code:

CREATE PROCEDURE [SP_Name]
WITH ENCRYPTION
AS

SELECT
[CustomerID], [CompanyName]
FROM
[Northwind].[dbo].[Customers]

If your stored procedure has some parameters, you can simply write ‘WITH ENCRYPTION’ after the parameters:

CREATE PROCEDURE [SP_with_Parameters]

@Customer VARCHAR(25),
@CustomerID INT

WITH ENCRYPTION
AS

/* Rest of the SP here.. */

Now when the user will attempt to view the stored procedure, an error will occur:

Error 20585: [SQL-DMO] Encrypted object is not transferable, and script can not be generated.

Rate this topic:

(No Ratings Yet)

 Loading ...