How to encrypt all existing stored procedures of a

问题:

Is there any possibility to encrypt all existing stored procedures of a SQL Server 2008 database AFTER they have been created via an SQLCMD script?

The reason I want to do this is the following:
I'd like to develop the stored procedures without encryption so I can easily click on "Modify" in SQL Server Management Studio to check their contents.
However, for the deployment I'd like to encrypt them so I thought that maybe I could write a script which encrypts them only after they're created. For dev systems I simply wouldn't run the script while on end-user systems the script would be run.

回答1:

I have the same problem.

My solution is to put "-- WITH ENCRYPTION" in all of my stored procedures. This version is used by developers and stored in source control.

I then use a tool (like sed) in my build to replace "-- WITH ENCRYPTION" with "WITH ENCRYPTION" on the files before I send them to be installed.

For a pure SQL solution you could use REPLACE.

回答2:

You might want to check Encrypting all the Stored Procedures of a Database :

If you ever decide that you need to protect your SQL Stored Procedures, and thought encrypting was a good idea, BE VERY CAREFUL!!! Encrypting Database stored procedures SHOULD NOT be done without having backup files or some sort of Source Control for the stored procedures. The reason I say this is because, once they are encrypted, there is no turning around. (Yes, there are third party tools that will decrypt your code, but Why go through that trouble.)

This trick is something I developed because my company needed to host the application on a different server, and we were concerned about our code being compromised. So, to deliver the database, we decided to encrypt all out stored procedures. Having over a hundred procedures written, I didn't want to open each procedure and paste 'WITH ENCRYPTION' in each and every stored procedure. (For those of you who do not know how to encrypt, refer How Do I Protect My Stored Procedure Code[^]). So I decided to make my own little C# application that did the same.

This application is a console application made using Visual Studio 2005 and SQL server 2005. The input parameters are database name, Server address, database username and password. Once you are able to provide these details, you are ready to have all your stored procedures encrypted.

I have put the code of my application here as is. For this code to work, you will need to add an "Microsft.SQlserver.SMO" refrence to the application, so that the classes such as "Database" and "StoredProcedure" are accessible.

BEFORE YOU DO THIS, TAKE A BACKUP!!!!!!!

//Connect to the local, default instance of SQL Server. 
string DB = "";
ServerConnection objServerCOnnection = new ServerConnection();
objServerCOnnection.LoginSecure = false;
Console.WriteLine("Enter name or IP Address of the Database Server.");
objServerCOnnection.ServerInstance = Console.ReadLine();
Console.WriteLine("Enter name of the Database");
DB = Console.ReadLine();
Console.WriteLine("Enter user id");
objServerCOnnection.Login = Console.ReadLine();
Console.WriteLine("Enter Password");
objServerCOnnection.Password = Console.ReadLine();
Console.WriteLine(" ");
Server srv = new Server();
try // Check to see if server connection details are ok.
{
   srv = new Server(objServerCOnnection);
   if (srv == null)
   {
      Console.WriteLine("Server details entered are wrong,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Server details entered are wrong,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
Database db = new Database();
try // Check to see if database exists.
{
   db = srv.Databases[DB];
   if (db == null)
   {
      Console.WriteLine("Database does not exist on the current server,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Database does not exist on the current server,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
string allSP = "";

for (int i = 0; i < db.StoredProcedures.Count; i++)
{
   //Define a StoredProcedure object variable by supplying the parent database 
   //and name arguments in the constructor. 
   StoredProcedure sp;
   sp = new StoredProcedure();
   sp = db.StoredProcedures[i];
   if (!sp.IsSystemObject)// Exclude System stored procedures
   {
      if (!sp.IsEncrypted) // Exclude already encrypted stored procedures
      {
         string text = "";// = sp.TextBody;
         sp.TextMode = false;
         sp.IsEncrypted = true;
         sp.TextMode = true;
         sp.Alter();

         Console.WriteLine(sp.Name); // display name of the encrypted SP.
         sp = null;
         text = null;
      }
   }
}

回答3:

WITH ENCRYPTION means that the code behind the proc is not stored in the SysComments table.

You could write a script that does a exec sp_helptext 'MyProcName' and gets the contents into a VarChar (MAX) so it can hold multiline / large procedures easily and then modifiy the procedure from it's original state

CREATE MyProcName AS

SELECT SecretColumns From TopSecretTable

change CREATE to ALTER and AS surrounded by space or tab or newline (good place to use Regular Expressions) to WITH ENCRYPTION AS

ALTER MyProcName WITH ENCRYPTION AS

SELECT SecretColumns From TopSecretTable

This will hide all code for the stored proc on the production server.

You can put this in a LOOP or a CURSOR (not really a set based operation IMHO) for all objects of a specific type and/or naming convention that you want to encrypt, and run it every time you deploy.

回答4:

I would recommend creating the sproc in a multi-line string variable and then inserting or altering it using sp_executesql. The only annoying downside to this approach is doubling of single quotes for strings.

DECLARE @action varchar(max);
SET @action = 'CREATE'; /* or "ALTER" */

DECLARE @withEncryption varchar(max);
SET @withEncryption = ''; /* or "WITH ENCRYPTION" */

DECLARE @sql varchar(max);
SET @sql = @action + ' PROCEDURE dbo.Something'
    (
        ....
    ) ' + @withEncryption +
    ' AS
    BEGIN
        DECLARE @bob varchar(10);
        SET @bob = ''Bob'';
        ....
    END;
    ';

EXEC sp_executesql @statement = @sql;

[Note the whitespace around the variables.]

All of my scripts use this method, which works well once you get used to the quote doubling thing.

I also use a batch file to call the script, and SQLCMD-mode command line variables to select various behaviours, which makes it repeatable and easy to test.

回答5:

Use This Query which Encrypt All Procedures in database

    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 M AKE 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

回答6:

I wrote a cursor, steps through and encrypts most objects.

                            DECLARE cur_ENCRYPT_ANTHING CURSOR READ_ONLY
                            FOR
                                    SELECT  STUFF(src.definition,
                                                  CASE WHEN CHARINDEX('AS' + CHAR(13),src.definition,1) = 0
                                                       THEN CASE WHEN CHARINDEX('AS ' + CHAR(13),src.definition,1) = 0 THEN CHARINDEX('AS ',src.definition,1)
                                                                 ELSE CHARINDEX('AS ' + CHAR(13),src.definition,1)
                                                            END
                                                       ELSE CHARINDEX('AS' + CHAR(13),src.definition,1)
                                                  END,3,'WITH ENCRYPTION AS' + CHAR(13))
                                    FROM    (SELECT o.name
                                             ,      STUFF(RIGHT(sm.definition,LEN(sm.definition) - CHARINDEX('CREATE ',sm.definition,1) + 1),1,6,'ALTER') AS definition
                                             FROM   sys.sql_modules AS sm
                                                    JOIN sys.objects AS o ON sm.object_id = o.object_id
                                             WHERE  CAST(CASE WHEN sm.definition IS NULL THEN 1
                                                              ELSE 0
                                                         END AS BIT) = 0
                                                    AND type <> 'TR'
                                            ) AS src








                            DECLARE @VLS NVARCHAR(MAX)
                            OPEN cur_ENCRYPT_ANTHING

                            FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS
                            WHILE (@@fetch_status <> -1)
                                  BEGIN
                                        IF (@@fetch_status <> -2)
                                           BEGIN
                                                 BEGIN TRY
                                                       EXEC (@VLS)

                                                 END TRY
                                                 BEGIN CATCH
                                                       PRINT ERROR_MESSAGE()
                                                       PRINT ''

                                                       PRINT @VLS
                                                 END CATCH
                                           END
                                        FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS
                                  END

                            CLOSE cur_ENCRYPT_ANTHING
                            DEALLOCATE cur_ENCRYPT_ANTHING

回答7:

1) I export Create code for SP and functions. Keep it backed up. for example D:\SP2.sql"

2) this transact SQL code, generate the script to delete existing sP & Functions

SELECT 'DROP PROCEDURE  [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'  as A
FROM sys.procedures p
union
SELECT  'DROP FUNCTION ' + [name]  
FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0
order by a

3) This Poweshell code replace

AS
BEGIN

by

WITH ENCRYPTION 
AS
BEGIN

The code

$File = "D:\SP2.sql"
$File2 = $File.Replace("SP2.sql","SP-WithEncrypt.sql")
$sortie=""
$SP = get-content -path $file
echo $SP.Count
For ($i = 0 ; $i -le $SP.Count)
{ if ($sp[$i] -eq "AS" -and $sp[$i+1] -eq "BEGIN")
   { $AEcrire = "`nWITH ENCRYPTION `n AS `n BEGIN"
   $i+=1 
          }
   else
   {$AEcrire =$sp[$i]
   }
   $sortie += "`n$AEcrire"

 $i+=1 
 $SP.Count-$i
}

$sortie| out-file $File2

Would be faster with a .replace( ,), but problem with End of lines...

4) run the SP-WithEncrypt.sql in SSMS

回答8:

I have made an update to one of the above answers by removing the dependency on the initial Begin Tag. I had a situation where not all my stored procedures had BEGIN and END.

I used the AS clause instead and also used a case sensitive version of the charindex (by adding a collation)

Its not a perfect solution but helped in getting more of my stored procedures encrypted.

Here is my updated code:

            IF 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] = 'CaseSensitiveIndex'

             AND xtype = 'FN' ) 

             BEGIN
                

             EXEC (
             'CREATE FUNCTION dbo.CaseSensitiveIndex(@source nvarchar(max), @pattern VARCHAR(50))
            RETURNS int
            BEGIN  
                return   CHARINDEX(@pattern COLLATE Latin1_General_CS_AS, @source COLLATE Latin1_General_CS_AS) 
            END; '
            )
            end


             IF NOT EXISTS ( SELECT [name]

             FROM sysobjects

             WHERE [name] = 'ce_LastIndexOf'

             AND xtype = 'FN' ) 

             BEGIN

                

             EXEC

             ( 'CREATE FUNCTION ce_LastIndexOf 

                (@strValue VARCHAR(max),

                @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=(select dbo.CaseSensitiveIndex(@tempproc, 'AS'))


             if(@beginindex=0) begin set @beginindex=( SELECT dbo.ce_lastindexof(@tempproc, 'AS'))end
             SELECT @header = SUBSTRING(@tempproc, @procindex,

             @beginindex )

             SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS')

             - 2

             )

             SELECT @replacetext = STUFF(@header, @asindex, 3,

             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 where encryptstatus =0