/*******************************************
* SQL加密自定义函数、存储过程、触发器的还原
* obj为要还原的对象，如：自定义函数名、存储过程名、触发器名
* EXEC sp_decrypt obj
*******************************************/
CREATE PROCEDURE 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),@sql2 varchar(8000)', @sql1 = @sql1, @sql2 = @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
--存储过程结束
GO
联系客服
