Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Encrypt All Stored procedures at once

By Chris Morton,

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

Total article views: 3198 | Views in the last 30 days: 1
 
Related Articles
FORUM

UDF to create a counter

row counter

FORUM

Wierd Counter!

Please help me to create a conditional counter

FORUM

counters

counters help

BLOG

SQL 2005 or above – Find Transaction Rate on a busy OLTP Database

Change Database name of a below mentioned query DECLARE @cntr_value1 bigint DECLARE @cntr_value2 ...

FORUM

How to declare parameters inside "select"?

select asset.asset_name asset_name (varchar(100))?

Tags
encryption    
metadata    
object_id    
stored procedures    
with encryption    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones