下載:
附件
SQL2008,SQL2005存儲過程解密
第一步操作步驟: ' 程序 ' -> ' Sql Server2005 ' -> ' 配置工具 ' -> ' Sql Server 外圍應用配置器 ' -> ' 功能的外圍應用配置器 ' -> ' DataBase Engine ' -> ' DAC ' -> ' 啟用遠程DAC '
第二步:啟動SQL Server Browser 服務
第三部使用DAC模式登陸(關鍵就在這個地方)
1 登陸SQL Server Management Studio
2 ,新建查詢,在彈出的連接對話框中,服務器名稱改為 ADMIN:服務器名稱;然后,選項 -> 連接屬性 -> 連接到數據庫 改為加密存儲過程所在的數據庫 -> 連接即可.(只能有一個查詢界面)
3 創建存儲過程 sp_windbi$decrypt (下文)
4 執行 exec sp_windbi$Decrypt ' 你要解密的存儲過程名 ' , 0
Create PROCEDURE [ dbo ] . [ sp_windbi$decrypt ]
( @procedure sysname = NULL , @revfl int = 1 )
AS
/**//*
王成輝翻譯整理,轉貼請注明出自微軟BI開拓者www.windbi.com
調用形式為:
exec dbo.sp__windbi$decrypt @procedure,0
如果第二個參數使用1的話,會給出該存儲過程的一些提示。
--版本4.0 修正存儲過程過長解密出來是空白的問題
*/
SET NOCOUNT ON
IF @revfl = 1
BEGIN
PRINT ' 警告:該存儲過程會刪除并重建原始的存儲過程。 '
PRINT ' 在運行該存儲過程之前確保你的數據庫有一個備份。 '
PRINT ' 該存儲過程通常應該運行在產品環境的一個備份的非產品環境下。 '
PRINT ' 為了運行這個存儲過程,將參數@refl的值更改為0。 '
RETURN 0
END
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 ) / 2 )
BEGIN
-- 真的和假的和加密的假的進行異或處理
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
-- 通過sp_helptext邏輯向表#output里插入變量
insert #output (real_decrypt) select @real_decrypt_01
-- select real_decrypt AS '#output chek' from #output --測試
-- -------------------------------------
-- 開始從sp_helptext提取
-- -------------------------------------
declare @dbname sysname
, @BlankSpaceAdded int
, @BasePos int
, @CurrentPos int
, @TextLength int
, @LineId int
, @AddOnLen int
, @LFCR int -- 回車換行的長度
, @DefinedLength int
, @SyscomText nvarchar ( max )
, @Line nvarchar ( 255 )
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 -- 跟蹤行結束的空格。注意Len函數忽略了多余的空格
CREATE TABLE #CommentText
(LineId int
, Text nvarchar ( 255 ) collate database_default)
-- 使用#output代替sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY
-- 獲取文本
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
-- 通過回車查找行的結束
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 <= @TextLength
BEGIN
-- 如果@Lines長度的新值大于定義的長度
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
-- -------------------------------------
-- 結束從sp_helptext提取
-- -------------------------------------
-- 刪除用短橫線創建的存儲過程并重建原始的存儲過程
ROLLBACK TRAN
DROP TABLE #output
go
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
