共计 5499 个字符，预计需要花费 14 分钟才能阅读完成。
SQLSERVER 中怎么解密存储过程，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
加密测试的存储过程
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE TYPE= P AND NAME= P_TEST)
DROP PROCEDURE P_TEST
GO
CREATE PROCEDURE P_TEST(@USERNAME VARCHAR(20),@MSG VARCHAR(20) OUTPUT)
WITH ENCRYPTION
AS
BEGIN
IF(SELECT COUNT(1) FROM Custs WHERE NAME=@USERNAME) 0
SET @MSG= 此用户名存在
ELSE
SET @MSG= 此用户名不存在
END
解密的存储过程
Create PROCEDURE Decryption(@procedure sysname = NULL)
AS
SET NOCOUNT ON
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int
select @maxColID = max(subobjid) FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–select @maxColID as Rows in sys.sysobjvalues
select @procNameLength = datalength(@procedure) + 29
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)
declare @objtype varchar(2),@ParentName nvarchar(max)
select @real_decrypt_01a =
– 提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称
select @objtype=type,@parentname=object_name(parent_object_id)
from sys.objects where [object_id]=object_id(@procedure)
— 从 sys.sysobjvalues 里提出加密的 imageval 记录
SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid)
– 创建一个临时表
create table #output ([ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
– 开始一个事务，稍后回滚
BEGIN TRAN
– 更改原始的存储过程，用短横线替换
if @objtype= P
SET @fake_01= ALTER PROCEDURE + @procedure + WITH ENCRYPTION AS select 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= FN
SET @fake_01= ALTER FUNCTION + @procedure + () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */ END
else if @objtype= V
SET @fake_01= ALTER view + @procedure + WITH ENCRYPTION AS select 1 as col
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= TR
SET @fake_01= ALTER trigger + @procedure + ON +@parentname+ WITH ENCRYPTION AFTER INSERT AS RAISERROR (N ,16,10)
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
EXECUTE (@fake_01)
– 从 sys.sysobjvalues 里提出加密的假的
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid )
if @objtype= P
SET @fake_01= Create PROCEDURE + @procedure + WITH ENCRYPTION AS select 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= FN
SET @fake_01= CREATE FUNCTION + @procedure + () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */ END
else if @objtype= V
SET @fake_01= Create view + @procedure + WITH ENCRYPTION AS select 1 as col
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= TR
SET @fake_01= Create trigger + @procedure + ON +@parentname+ WITH ENCRYPTION AFTER INSERT AS RAISERROR (N ,16,10)
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
– 开始计数
SET @intProcSpace=1
– 使用字符填充临时变量
SET @real_decrypt_01 = replicate(cast( A as nvarchar(max)), (datalength(@real_01) /2 ))
– 循环设置每一个变量，创建真正的变量
– 每次一个字节
SET @intProcSpace=1
– 如有必要，遍历每个 @real_xx 变量并解密
WHILE @intProcSpace =(datalength(@real_01) begin= set= real_decrypt_01= intprocspace= @intProcSpace+1 end= insert= output= select= –select= real_decrypt= as= from= –= ————————————-= declare= dbname= sysname= blankspaceadded= 0 int= basepos= currentpos= textlength= lineid= 1 addonlen= lfcr= 2 definedlength= 255 syscomtext= line= create= table= commenttext= text= collate= sys.sysobjvalues= ms_crs_syscom= cursor= local= for= order= by= ident= read= only= open= fetch= next= into= while= fetch_status= = 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
– 通过回车查找行的结束
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
– 如果找到回车
IF @CurrentPos != 0
BEGIN
– 如果 @Lines 的长度的新值比设置的大就插入 @Lines 目前的内容并继续
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
(@LineId,
isnull(@Line, N) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N ))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N )
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES(@LineId, @Line)
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
– 如果回车没找到
BEGIN
IF @BasePos = 1= textlength= begin= while= blankspaceadded= @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength –
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
(@LineId,
isnull(@Line, N) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N ))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1), N )
if LEN(@Line) @DefinedLength and charindex(,
@SyscomText, @TextLength+1 ) 0
BEGIN
SELECT @Line = @Line + , @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES(@LineId, @Line)
select Text from #CommentText order by LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
— ————————————-
– 结束从 sp_helptext 提取
— ————————————-
– 删除用短横线创建的存储过程并重建原始的存储过程
ROLLBACK TRAN
DROP TABLE #output
GO
启用 DAC
SP_CONFIGURE remote admin connections
GO
—-0：仅允许本地连接使用 DAC，1：允许远程连接使用 DAC
SP_CONFIGURE remote admin connections , 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
文件—— 新建—— 数据库引擎查询
服务器名称：admin:. 或者 admin: 服务器名称
登录进去执行解密操作
USE TEST
EXEC Decryption P_TEST
GO
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注丸趣 TV 行业资讯频道，感谢您对丸趣 TV 的支持。
