DanBrother的部落格

Encrypting and Decrypting a Stored Procedure (T-SQL)
 

-- Create a stored procedure named "sp_test_encryption"
create procedure sp_test_encryption
with encryption
as
 print 'This stored procedure has been encrypted!'
GO

-- Execute the stored procedure
exec sp_test_encryption

This stored procedure has been encrypted!

-- Enable remote connections using DAC (Dedicated Administrator Connection)
exec sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE

-- Open a new query using Remote DAC (put admin: in front of the Server Name)
File -> New -> Database Engine Query -> admin:ServerName

-- Show the Original Decrypted content of the Stored Procedure
DECLARE @objectname VARCHAR(30) = 'sp_test_encryption'
DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = @objectname )
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.' + @objectname + ' WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = @objectname )

SET @procedureHeader = N'CREATE PROCEDURE dbo.' + @objectname + ' WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
  SET @decryptedChar = 
      NCHAR(
        UNICODE(SUBSTRING(
           @encrypted, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @procedureHeader, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @blankEncrypted, @cnt, 1))
     )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

Decryption Output:
CREATE procedure sp_test_encryption  with encryption  as   print 'This stored procedure has been encrypted!' 

-- Disable remote connections using DAC (Dedicated Administrator Connection)
exec sp_configure 'remote admin connections', 0
RECONFIGURE WITH OVERRIDE