Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Beginner
Feb 2, 2001

Encrypting Stored Procedures


When creating stored procedures, there will be occasions where a certain procedure definition should not be viewed. Using the WITH ENCRYPTION clause stores the procedure definition in an unreadable form. After the definition of the stored procedure is encrypted, it cannot be decrypted nor viewed by anyone, including the owner of the stored procedure and the system administrator.

Sample code:
 
--------------- Create Stored Procedure --------------
CREATE PROC SPtest
 WITH ENCRYPTION
AS

SELECT [CustomerID], [CompanyName], [ContactName],
       [ContactTitle], [Address], [City], [Region],
       [PostalCode], [Country], [Phone], [Fax]
FROM [Northwind].[dbo].[Customers]
------------------------------------------------------


--------------- Execute Stored Procedure -------------
DECLARE @RC int
-- Set parameter values
EXEC @RC = [Northwind].[dbo].[SPtest]
------------------------------------------------------

When a user attempts to open the created stored procedure using SQL Server 2000, Error 20585 is produced stating that the encrypted object is not transferable, and script cannot be generated.
Rick Codrington
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap