SQL Server 存储过程with encryption解密

先把下面的存储过程在要解密的存储过程的数据库上创建

然后新建查询 再右击连接->更改连接，然后在服务器名称输入admin:127.0.0.1，点击连接

输入以下代码更改要解密的数据库

exec dbo.sp__procedure$decrypt ‘数据库.存储过程’,1

看完提示后保证都做好了备份，再把1改成0再次运行

2000版本的直接创建存储，再进行解密就行了

exec sp_decrypt ‘数据库.存储过程’

由于版本不一样，请选择对应的存储过程，不然会出现错误

2008的解密存储过程

CREATE PROCEDURE [dbo].[sp__procedure$decrypt]
(@procedure sysname = NULL, @revfl int = 1)
AS
SET NOCOUNT ON

IF @revfl = 1
BEGIN
PRINT ‘注意:此过程删除并重新构建原始存储过程。’
PRINT ‘在运行此过程之前备份数据库。’
PRINT ‘理想情况下，此过程应该在该过程的非生产副本上运行。’
PRINT ‘要运行此过程，请将@revfl参数更改为0’
RETURN 0
END

DECLARE @intProcSpace bigint
,@t bigint
,@maxColID smallint
,@intEncrypted tinyint
,@procNameLength int

select @maxColID = max(subobjid)
–//,@intEncrypted = imageval
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure)
GROUP BY imageval

–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29

DECLARE @real_01 nvarchar(max)
DECLARE @real_02 nvarchar(max)
DECLARE @real_03 nvarchar(max)
DECLARE @real_04 nvarchar(max)
DECLARE @real_05 nvarchar(max)

DECLARE @fake_01 nvarchar(max)
,@fake_02 nvarchar(max)
,@fake_03 nvarchar(max)
,@fake_04 nvarchar(max)
,@fake_05 nvarchar(max)

DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @fake_encrypt_02 nvarchar(max)
DECLARE @fake_encrypt_03 nvarchar(max)
DECLARE @fake_encrypt_04 nvarchar(max)
DECLARE @fake_encrypt_05 nvarchar(max)

DECLARE @real_decrypt_01 nvarchar(max)
,@real_decrypt_01a nvarchar(max)
,@real_decrypt_02 nvarchar(max)
,@real_decrypt_02a nvarchar(max)
,@real_decrypt_03 nvarchar(max)
,@real_decrypt_03a nvarchar(max)
,@real_decrypt_04 nvarchar(max)
,@real_decrypt_04a nvarchar(max)
,@real_decrypt_05 nvarchar(max)
,@real_decrypt_05a nvarchar(max)

select @real_decrypt_01a = ‘’
,@real_decrypt_02a = ‘’
,@real_decrypt_03a = ‘’
,@real_decrypt_04a = ‘’
,@real_decrypt_05a = ‘’

– extract the encrypted imageval rows from sys.sysobjvalues
SELECT @real_01=substring(imageval,1,8000)
,@real_02=substring(imageval,8001,16000)
,@real_03=substring(imageval,16001,24000)
,@real_04=substring(imageval,24001,32000)
,@real_05=substring(imageval,32001,40000)
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure) and valclass = 1 and subobjid = 1

– create this table for later use
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX)
)

– We’ll begin the transaction and roll it back later
BEGIN TRAN
– alter the original procedure, replacing with dashes
SET @fake_01=‘ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(’-’, 40003 - @procNameLength)

EXECUTE (@fake_01)

– extract the encrypted fake imageval rows from sys.sysobjvalues
SELECT @fake_encrypt_01=substring(imageval,1,8000)
,@fake_encrypt_02=substring(imageval,8001,16000)
,@fake_encrypt_03=substring(imageval,16001,24000)
,@fake_encrypt_04=substring(imageval,24001,32000)
,@fake_encrypt_05=substring(imageval,32001,40000)
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure) and valclass = 1 and subobjid =