建好sp后，在“连接到数据库引擎”对话框的“服务器名称”框中，键入 ADMIN:，并在其后继续键入服务器实例的名称。例如，若要连接到名为 ACCTPAYABLE 的服务器实例，请键入 ADMIN:ACCTPAYABLE。然后再调用此存储过程来查看。
CREATE PROCEDURE dbo.sp__procedure$decrypt
(@procedure sysname = NULL, @revfl int = 1)
AS
SET NOCOUNT ON
IF @revfl = 1
BEGIN
PRINT 'CAUTION: THIS PROCEDURE DELETES AND REBUILDS THE ORIGINAL STORED PROCEDURE.'
PRINT ' MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING THIS PROCEDURE.'
PRINT ' IDEALLY, THIS PROCEDURE SHOULD BE RUN ON A NON-PRODUCTION COPY OF THE PROCEDURE.'
PRINT ' To run the procedure, change the @revfl parameter to 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 = 1
SET @fake_01='CREATE PROCEDURE '+ @procedure +' WITH ENCRYPTION AS '
+ REPLICATE('-', 40003 - @procNameLength)
--start counter
SET @intProcSpace=1
--fill temporary variable with with a filler character
SET @real_decrypt_01 = replicate(N'A', (datalength(@real_01) /2 ))
--loop through each of the variables sets of variables, building the real variable
--one byte at a time.
SET @intProcSpace=1
-- Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_01)/2)
BEGIN
--xor real & fake & fake encrypted
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
--one byte at a time.
SET @intProcSpace=1
-- Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_02)/2)
BEGIN
--xor real & fake & fake encrypted
SET @real_decrypt_02 = stuff(@real_decrypt_02, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_02, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_02, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_02, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
--one byte at a time.
SET @intProcSpace=1
-- Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_03)/2)
BEGIN
--xor real & fake & fake encrypted
SET @real_decrypt_03 = stuff(@real_decrypt_03, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_03, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_03, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_03, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
--one byte at a time.
SET @intProcSpace=1
-- Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_04)/2)
BEGIN
--xor real & fake & fake encrypted
SET @real_decrypt_04 = stuff(@real_decrypt_04, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_04, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_04, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_04, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
--one byte at a time.
SET @intProcSpace=1
-- Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_05)/2)
BEGIN
--xor real & fake & fake encrypted
SET @real_decrypt_05 = stuff(@real_decrypt_05, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_05, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_05, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_05, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
-- Load the variables into #output for handling by sp_helptext logic
INSERT INTO #output (real_decrypt)
SELECT @real_decrypt_01
UNION ALL
SELECT @real_decrypt_02
UNION ALL
SELECT @real_decrypt_03
UNION ALL
SELECT @real_decrypt_04
UNION ALL
SELECT @real_decrypt_05
-- select real_decrypt AS '#output chek' from #output -- Testing
-- -------------------------------------
-- Beginning of extract from sp_helptext
-- -------------------------------------
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int --lengths of line feed carriage return
,@DefinedLength int
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 --Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)
-- use #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt
from #output
ORDER BY ident
FOR READ ONLY
-- Else get the text.
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
--If carriage return found
IF @CurrentPos != 0
BEGIN
--If new value for @Lines length will be > then the
--set length then insert current contents of @line
--and proceed.
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
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
--If new value for @Lines length will be > then the
--defined length
--
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @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
-- -------------------------------------
-- End of extract from sp_helptext
-- -------------------------------------
-- Drop the procedure that was setup with dashes and rebuild it with the good stuff
-- Version 1.1 mod; makes rebuilding hte proc unnecessary
ROLLBACK TRAN
DROP TABLE #output
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO