分享:将存储过程批量进行加密.可保证95%以上成功率

USE MyTargetDB 
go 
IF object_id ('wfp_before_add_password_backup') IS NULL 
SELECT  o.name AS proc_name ,
        s.definition
INTO    tempdb.dbo.wfp_before_add_password_backup
FROM    sys.sql_modules s ,
        sys.objects o
WHERE   o.object_id = s.object_id and s.definition is not null 
        AND o.type = 'P'

IF OBJECT_ID('tempdb.dbo.new_definition_error') IS NOT NULL 
    DROP TABLE  tempdb.dbo.new_definition_error 
    --执行中,出现错误的收集到本表中
CREATE  TABLE tempdb.dbo.new_definition_error
    (
      proc_name NVARCHAR(128) ,
      errmsg NVARCHAR(MAX) ,
      remark CHAR(1)
    )
IF OBJECT_ID('tempdb.dbo.wfp_before_add_password_with_new_definition') IS NOT NULL 
    DROP TABLE    tempdb.dbo.wfp_before_add_password_with_new_definition
SELECT  o.name AS proc_name ,
        s.definition ,
        CAST(NULL AS NVARCHAR(MAX)) add_password_definition
INTO    tempdb.dbo.wfp_before_add_password_with_new_definition --   将加密前的过程代码,备份;以防不测
FROM    sys.sql_modules s ,
        sys.objects o
WHERE   o.object_id = s.object_id
        AND o.type = 'P'
        AND o.name LIKE 'pr_%'
		and s.definition is not null ;--加密的过滤除去





DECLARE @definition NVARCHAR(MAX) ,
    @proc_name NVARCHAR(128) ,
    @new_definition NVARCHAR(MAX)
DECLARE @newline VARCHAR(2)
SET @newline = CHAR(13) + CHAR(10)
--游标,每个过程单独处理
DECLARE c CURSOR
FOR
    SELECT  proc_name ,
            definition
    FROM    tempdb.dbo.wfp_before_add_password_with_new_definition

OPEN c 
FETCH NEXT FROM c INTO @proc_name, @definition
WHILE @@FETCH_STATUS = 0 
    BEGIN 
		--将)AS  处理掉
        SET @definition = REPLACE(SUBSTRING(@definition, 1, 500), ')AS', ') AS') + SUBSTRING(@definition, 501, 99999999)
        DECLARE @c1 NVARCHAR(MAX) ---参数串
        SET @c1 = '%' + ( SELECT    name + '%'
                          FROM      sys.parameters
                          WHERE     object_id = OBJECT_ID(@proc_name)
                          ORDER BY  parameter_id ASC
                        FOR
                          XML PATH('')
                        )
        --PRINT '@c1 = ' + @c1 ---找到第一个参数位置
        DECLARE @position1 INT 
        SELECT  @position1 = PATINDEX(@c1, @definition) 
        --PRINT '参数最开始的位置：@position1 = ' + CAST(@position1 AS VARCHAR) 

        DECLARE @end_parameter NVARCHAR(128) ,
            @len_end_parameter INT 
        SET @end_parameter = ISNULL(( SELECT TOP 1
                                                name
                                      FROM      sys.parameters
                                      WHERE     object_id = OBJECT_ID(@proc_name)
                                      ORDER BY  parameter_id DESC
                                    ), '')
        DECLARE @position_end INT 
        DECLARE @position_split INT 


        --PRINT REPLICATE('*', 100)
        SET @position_end = PATINDEX('%' + @end_parameter + '%AS%', @definition)

		--- 定位到最后一个参数末尾位置
        SET @position_end = @position_end + LEN(@end_parameter)
        --- 确定分隔位置
        SET @position_split = CHARINDEX('as', @definition, @position_end)
        SET @new_definition = SUBSTRING(@definition, 1, @position_split - 1) + @newline + ' WITH ENCRYPTION ' + @newline + 
	SUBSTRING(@definition, @position_split, 99999999)
        --PRINT @new_definition
        BEGIN TRY 
        ----保证语句执行错误后，可以保留原不能替换语句，加事务
            BEGIN TRAN new_definition
            IF @new_definition IS NOT NULL 
                BEGIN 
                    EXEC (' drop proc [' + @proc_name + ']' )--先DROP
                    EXEC (@new_definition) --再CREATE 
                    UPDATE  tempdb.dbo.wfp_before_add_password_with_new_definition
                    SET     add_password_definition = @new_definition -- 将新定义的语句，收集起来
                    WHERE   proc_name = @proc_name
                END 
            IF @new_definition IS  NULL  -- 拼串时发生错误时 NULL ,new_definition_error 查找原因
                BEGIN 
                    INSERT  INTO tempdb.dbo.new_definition_error
                            ( proc_name, remark )
                            SELECT  @proc_name ,
                                    '1'
                END 
            COMMIT TRAN new_definition
        END TRY 
        BEGIN CATCH 
            ROLLBACK TRAN new_definition
            INSERT  INTO tempdb.dbo.new_definition_error  --发生错误时，回滚；收集错误信息
                    ( proc_name, errmsg )
                    SELECT  @proc_name ,
                            ERROR_MESSAGE()
        END CATCH 

        FETCH NEXT FROM c INTO @proc_name, @definition
    END 
CLOSE c 
DEALLOCATE c 

--存在未加密成功的过程，强制人为返回错误
IF EXISTS ( SELECT  *
			FROM    sys.sql_modules s ,
					sys.objects o
			WHERE   o.object_id = s.object_id
					AND o.type = 'P'
					AND o.name LIKE 'pr_%'
					and s.definition is not null ) 
    BEGIN 
        select  '本次加密操作未完全成功！请手动执行加密
		SELECT  OBJECT_NAME(a.object_id) ,
				a.definition
		FROM    wfp.sys.sql_modules a ,
				wfp.sys.objects b
		WHERE   a.definition IS NOT NULL and b.name LIKE ''pr_%''
				AND a.object_id = b.object_id
				AND b.type = ''P''
        查看失败过程,然后手工操作 
		' as error_msg 
      
    END