解密的储存过程都来自网络,需要在DAC下使用.
create
PROCEDURE
[
dbo
]
.
[
sp_decrypt
]
(
@objectname
varchar
(
50
))
AS
begin
set
nocount
on
begin
tran
declare
@objectname1
varchar
(
100
),
@orgvarbin
varbinary
(
8000
)
declare
@sql1
nvarchar
(
4000
),
@sql2
varchar
(
8000
),
@sql3
nvarchar
(
4000
),
@sql4
nvarchar
(
4000
)
DECLARE
@OrigSpText1
nvarchar
(
4000
),
@OrigSpText2
nvarchar
(
4000
) ,
@OrigSpText3
nvarchar
(
4000
),
@resultsp
nvarchar
(
4000
)
declare
@i
int
,
@status
int
,
@type
varchar
(
10
),
@parentid
int
declare
@colid
int
,
@n
int
,
@q
int
,
@j
int
,
@k
int
,
@encrypted
int
,
@number
int
select
@type
=
xtype,
@parentid
=
parent_obj
from
sysobjects
where
id
=
object_id
(
@objectname
)
create
table
#
temp
(
number
int
,colid
int
,ctext
varbinary
(
8000
),encrypted
int
,status
int
)
insert
#
temp
SELECT
number
,colid,ctext,encrypted,status
FROM
syscomments
WHERE
id
=
object_id
(
@objectname
)
select
@number
=
max
(
number
)
from
#
temp
set
@k
=
0
while
@k
<=
@number
begin
if
exists
(
select
1
from
syscomments
where
id
=
object_id
(
@objectname
)
and
number
=
@k
)
begin
if
@type
=
'
P
'
set
@sql1
=
(
case
when
@number
>
1
then
'
ALTER PROCEDURE
'
+
@objectname
+
'
;
'
+
rtrim
(
@k
)
+
'
WITH ENCRYPTION AS
'
else
'
ALTER PROCEDURE
'
+
@objectname
+
'
WITH ENCRYPTION AS
'
end
)
if
@type
=
'
TR
'
begin
declare
@parent_obj
varchar
(
255
),
@tr_parent_xtype
varchar
(
10
)
select
@parent_obj
=
parent_obj
from
sysobjects
where
id
=
object_id
(
@objectname
)
select
@tr_parent_xtype
=
xtype
from
sysobjects
where
id
=
@parent_obj
if
@tr_parent_xtype
=
'
V
'
begin
set
@sql1
=
'
ALTER TRIGGER
'
+
@objectname
+
'
ON
'
+
OBJECT_NAME
(
@parentid
)
+
'
WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1
'
end
else
begin
set
@sql1
=
'
ALTER TRIGGER
'
+
@objectname
+
'
ON
'
+
OBJECT_NAME
(
@parentid
)
+
'
WITH ENCRYPTION FOR INSERT AS PRINT 1
'
end
end
if
@type
=
'
FN
'
or
@type
=
'
TF
'
or
@type
=
'
IF
'
set
@sql1
=
(
case
@type
when
'
TF
'
then
'
ALTER FUNCTION
'
+
@objectname
+
'
(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end
'
when
'
FN
'
then
'
ALTER FUNCTION
'
+
@objectname
+
'
(@a char(1)) returns char(1) with encryption as begin return @a end
'
when
'
IF
'
then
'
ALTER FUNCTION
'
+
@objectname
+
'
(@a char(1)) returns table with encryption as return select @a as a
'
end
)
if
@type
=
'
V
'
set
@sql1
=
'
ALTER VIEW
'
+
@objectname
+
'
WITH ENCRYPTION AS SELECT 1 as f
'
set
@q
=
len
(
@sql1
)
set
@sql1
=
@sql1
+
REPLICATE
(
'
-
'
,
4000
-
@q
)
select
@sql2
=
REPLICATE
(
'
-
'
,
8000
)
set
@sql3
=
'
exec(@sql1
'
select
@colid
=
max
(colid)
from
#
temp
where
number
=
@k
set
@n
=
1
while
@n
<=
CEILING
(
1.0
*
(
@colid
-
1
)
/
2
)
and
len
(
@sql3
)
<=
3996
begin
set
@sql3
=
@sql3
+
'
+@
'
set
@n
=
@n
+
1
end
set
@sql3
=
@sql3
+
'
)
'
exec
sp_executesql
@sql3
,N
'
@sql1 nvarchar(4000),@ varchar(8000)
'
,
@sql1
=
@sql1
,@
=
@sql2
end
set
@k
=
@k
+
1
end
set
@k
=
0
while
@k
<=
@number
begin
if
exists
(
select
1
from
syscomments
where
id
=
object_id
(
@objectname
)
and
number
=
@k
)
begin
select
@colid
=
max
(colid)
from
#
temp
where
number
=
@k
set
@n
=
1
while
@n
<=
@colid
begin
select
@OrigSpText1
=
ctext,
@encrypted
=
encrypted,
@status
=
status
FROM
#
temp
WHERE
colid
=
@n
and
number
=
@k
SET
@OrigSpText3
=
(
SELECT
ctext
FROM
syscomments
WHERE
id
=
object_id
(
@objectname
)
and
colid
=
@n
and
number
=
@k
)
if
@n
=
1
begin
if
@type
=
'
P
'
SET
@OrigSpText2
=
(
case
when
@number
>
1
then
'
CREATE PROCEDURE
'
+
@objectname
+
'
;
'
+
rtrim
(
@k
)
+
'
WITH ENCRYPTION AS
'
else
'
CREATE PROCEDURE
'
+
@objectname
+
'
WITH ENCRYPTION AS
'
end
)
if
@type
=
'
FN
'
or
@type
=
'
TF
'
or
@type
=
'
IF
'
SET
@OrigSpText2
=
(
case
@type
when
'
TF
'
then
'
CREATE FUNCTION
'
+
@objectname
+
'
(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end
'
when
'
FN
'
then
'
CREATE FUNCTION
'
+
@objectname
+
'
(@a char(1)) returns char(1) with encryption as begin return @a end
'
when
'
IF
'
then
'
CREATE FUNCTION
'
+
@objectname
+
'
(@a char(1)) returns table with encryption as return select @a as a
'
end
)
if
@type
=
'
TR
'
begin
if
@tr_parent_xtype
=
'
V
'
begin
set
@OrigSpText2
=
'
CREATE TRIGGER
'
+
@objectname
+
'
ON
'
+
OBJECT_NAME
(
@parentid
)
+
'
WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1
'
end
else
begin
set
@OrigSpText2
=
'
CREATE TRIGGER
'
+
@objectname
+
'
ON
'
+
OBJECT_NAME
(
@parentid
)
+
'
WITH ENCRYPTION FOR INSERT AS PRINT 1
'
end
end
if
@type
=
'
V
'
set
@OrigSpText2
=
'
CREATE VIEW
'
+
@objectname
+
'
WITH ENCRYPTION AS SELECT 1 as f
'
set
@q
=
4000
-
len
(
@OrigSpText2
)
set
@OrigSpText2
=
@OrigSpText2
+
REPLICATE
(
'
-
'
,
@q
)
end
else
begin
SET
@OrigSpText2
=
REPLICATE
(
'
-
'
,
4000
)
end
SET
@i
=
1
SET
@resultsp
=
replicate
(N
'
A
'
, (
datalength
(
@OrigSpText1
)
/
2
))
WHILE
@i
<=
datalength
(
@OrigSpText1
)
/
2
BEGIN
SET
@resultsp
=
stuff
(
@resultsp
,
@i
,
1
,
NCHAR
(
UNICODE
(
substring
(
@OrigSpText1
,
@i
,
1
))
^
(
UNICODE
(
substring
(
@OrigSpText2
,
@i
,
1
))
^
UNICODE
(
substring
(
@OrigSpText3
,
@i
,
1
)))))
SET
@i
=
@i
+
1
END
set
@orgvarbin
=
cast
(
@OrigSpText1
as
varbinary
(
8000
))
set
@resultsp
=
(
case
when
@encrypted
=
1
then
@resultsp
else
convert
(
nvarchar
(
4000
),
case
when
@status
&
2
=
2
then
uncompress(
@orgvarbin
)
else
@orgvarbin
end
)
end
)
print
@resultsp
set
@n
=
@n
+
1
end
end
set
@k
=
@k
+
1
end
drop
table
#
temp
rollback
tran
end
create
PROCEDURE
[
dbo
]
.
[
sp__windbi$decrypt
]
(
@procedure
sysname
=
NULL
,
@revfl
int
=
1
)
AS
SET
NOCOUNT
ON
IF
@revfl
=
1
BEGIN
PRINT
'
警告:该存储过程会删除并重建原始的存储过程。
'
PRINT
'
在运行该存储过程之前确保你的数据库有一个备份。
'
PRINT
'
该存储过程通常应该运行在产品环境的一个备份的非产品环境下。
'
PRINT
'
为了运行这个存储过程,将参数@refl的值更改为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
@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
'
+
REPLICATE
(
'
-
'
,
40003
-
@procNameLength
)
else
if
@objtype
=
'
FN
'
SET
@fake_01
=
'
ALTER FUNCTION
'
+
@procedure
+
'
() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/*
'
+
REPLICATE
(
'
*
'
,
datalength
(
@real_01
)
/
2
-
@procNameLength
)
+
'
*/ END
'
else
if
@objtype
=
'
V
'
SET
@fake_01
=
'
ALTER view
'
+
@procedure
+
'
WITH ENCRYPTION AS select 1 as col
/*
'
+
REPLICATE
(
'
*
'
,
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
(
'
*
'
,
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
'
+
REPLICATE
(
'
-
'
,
40003
-
@procNameLength
)
else
if
@objtype
=
'
FN
'
SET
@fake_01
=
'
CREATE FUNCTION
'
+
@procedure
+
'
() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/*
'
+
REPLICATE
(
'
*
'
,
datalength
(
@real_01
)
/
2
-
@procNameLength
)
+
'
*/ END
'
else
if
@objtype
=
'
V
'
SET
@fake_01
=
'
Create view
'
+
@procedure
+
'
WITH ENCRYPTION AS select 1 as col
/*
'
+
REPLICATE
(
'
*
'
,
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
(
'
*
'
,
datalength
(
@real_01
)
/
2
-
@procNameLength
)
+
'
*/
'
--
开始计数
SET
@intProcSpace
=
1
--
使用字符填充临时变量
SET
@real_decrypt_01
=
replicate
(N
'
A
'
, (
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
(
4000
)
,
@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