encript all sp

encript all sp mssql

CREATE TABLE #backup
(
  id BIGINT IDENTITY(1, 1),
  sptext NVARCHAR(MAX) NOT NULL,
  spname NVARCHAR(100) NOT NULL,
  encrypttext NVARCHAR(MAX) NULL,
  encryptstatus BIT NOT NULL
  DEFAULT ( 0 )
)
DECLARE @sptexttable TABLE
(
  id BIGINT IDENTITY(1, 1),
  sptext NVARCHAR(MAX),
  spname NVARCHAR(100)
)
INSERT INTO @sptexttable ( sptext, spname )
SELECT [text],[name] FROM syscomments
JOIN sysobjects ON syscomments.id = sysobjects.id
AND sysobjects.xtype = 'p'
DECLARE @sptext NVARCHAR(MAX)
DECLARE @spname NVARCHAR(100)
DECLARE @counter INT
SET @counter = 1
WHILE @counter <= ( SELECT MAX(id)
FROM @sptexttable
)
BEGIN
BEGIN TRY
 
INSERT INTO #backup ( sptext, spname )
SELECT sptext,spname FROM @sptexttable
WHERE id = @counter
END TRY
BEGIN CATCH
END CATCH

IF NOT EXISTS ( SELECT [name]
FROM sysobjects
WHERE [name] = 'ce_LastIndexOf'
AND xtype = 'FN' )
BEGIN
 
 
EXEC
( 'CREATE FUNCTION ce_LastIndexOf
(
  @strValue VARCHAR(4000),
  @strChar VARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE @index INT
 
 
SET @index = 0
 
 
WHILE CHARINDEX(@strChar, @strValue) > 0
BEGIN
SET @index = @index
+ CASE WHEN CHARINDEX(@strChar, @strValue) > 1
THEN ( LEN(@strValue) - LEN(SUBSTRING(@strValue,
CHARINDEX(@strChar, @strValue)
+ LEN(@strChar),
LEN(@strValue))) )
ELSE 1
END
SET @strValue = SUBSTRING(@strValue,
CHARINDEX(@strChar, @strValue)
+ LEN(@strChar), LEN(@strValue))
END
RETURN @index
END'
)
 
 
END
DECLARE @tempproc NVARCHAR(MAX)
DECLARE @procindex INT
DECLARE @beginindex INT
DECLARE @header NVARCHAR(MAX)
DECLARE @asindex INT
DECLARE @replacetext NVARCHAR(MAX)
 
SET @tempproc = ( SELECT sptext
FROM @sptexttable
WHERE id = @counter
)
 
IF ( SELECT CHARINDEX('CREATE PROC', UPPER(@tempproc))
) > 0
BEGIN
BEGIN TRY
SELECT @procindex = CHARINDEX('PROC', UPPER(@tempproc))
PRINT @procindex
SELECT @beginindex = CHARINDEX('BEGIN', UPPER(@tempproc))
PRINT @beginindex
SELECT @header = SUBSTRING(@tempproc, @procindex,
@beginindex - @procindex)
SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS')
- 2
)
SELECT @replacetext = STUFF(@header, @asindex, 10,
CHAR(13) + 'WITH ENCRYPTION'
+ CHAR(13) + 'AS' + CHAR(13))
SET @tempproc = REPLACE(@tempproc, @header, @replacetext)
 
END TRY
BEGIN CATCH
END CATCH
END
 
UPDATE @sptexttable
SET sptext = @tempproc
WHERE id = @counter
 
 
--PLAY HERE TO MAKE SURE ALL PROCS ARE ALTERED
UPDATE @sptexttable
SET sptext = ( SELECT REPLACE(sptext, 'CREATE PROC',
'ALTER PROC')
FROM @sptexttable
WHERE id = @counter
)
WHERE id = @counter
SELECT @sptext = sptext,
@spname = spname
FROM @sptexttable
WHERE id = @counter
BEGIN TRY
EXEC ( @sptext
)
UPDATE #backup
SET encrypttext = @sptext,
encryptstatus = 1
WHERE id = @counter
END TRY
BEGIN CATCH
PRINT 'the stored procedure ' + @spname
+ ' cannot be encrypted automatically'
END CATCH
SET @counter = @counter + 1
END
SELECT *
FROM #backup