本文介绍了一个SQL Server存储过程的批量加密方法。通过游标遍历所有用户定义的存储过程，并使用动态SQL更新其定义以添加WITH ENCRYPTION子句，从而实现加密。此脚本还包含了删除原有存储过程和重新创建加密后的存储过程的步骤。
DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
DECLARE @asbegin int
declare @now datetime
select @now = getdate()
DECLARE sp_cursor CURSOR FOR
SELECT object_name(id)
FROM sysobjects
WHERE xtype = 'P'
AND type = 'P'
AND crdate < @now
AND OBJECTPROPERTY(id, 'IsMSShipped')=0
OPEN sp_cursor
FETCH NEXT FROM sp_cursor
INTO @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID(@sp_name)
SELECT @asbegin = PATINDEX ( '%AS' + char(13) + '%', @sp_content)
SELECT @sp_content = SUBSTRING(@sp_content, 1, @asbegin - 1)
+ ' WITH ENCRYPTION AS'
+ SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT @sp_name = 'DROP PROCEDURE [' + @sp_name + ']'
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCH NEXT FROM sp_cursor
INTO @sp_name
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
扫一扫
882
到【灌水乐园】发言
