This code is designed particulary to encrypt all or most stored procs in your database
please remember to backup your procs first before encrypting them. this is intended to be used before you release your code to your client.
Should you have syntax error, in my experience this is caused by the browsers markup in the sql script code box. try copying and pasting this code into sql query, for some reason if you copy the code from directly below things are ok.
IIF OBJECT_ID('tempdb..#backup', 'U') IS NOT NULL
BEGIN
DROP TABLE #backup
END
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
i imagine my low ratings on this code is specifically due to the syntax errors got from the markup, if not please comment.
chris
