How to encrypt all existing stored procedures of a database

encrypt stored procedure with password
always encrypted stored procedures
redgate decrypt stored procedure
password protect stored procedure sql
how to lock stored procedure in sql server 2012
obfuscate mysql stored procedure
sp_helptext the text for object is encrypted
how to encrypt view in sql server

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.

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.

To Encrypt all stored procedures in a database. – SQLServerCentral, Is there any question in this statement? If there is can you try to explain what you are asking? How to encrypt stored procedure? Should you  To create a stored procedure with T-SQL, you use the CREATE PROCEDURE syntax. To encrypt it, you add the WITH ENCRYPTION argument. You can also use the same argument to encrypt an existing procedure when using ALTER PROCEDURE. When you encrypt a stored procedure in this way, the procedure’s text is converted to an obfuscated format.

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;
      }
   }
}

How To Encrypt All Store Procedure In SQL Server Database , By encrypting all stored procedures user can only execute but not see or modify any of the SPs. CREATE TABLE #backup ( id BIGINT IDENTITY(1, 1), sptext  So, to deliver the database, we decided to encrypt all our 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 to How Do I Protect My Stored Procedure Code). So I decided to make

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.

Encrypting all the Stored Procedures of a Database, Quick C# console application to encrypt all your database stored procedures. Hi All, I need to know how to encrypt all stored procedures at a time, instead of encrypting one by one. Is there any qury/shotcuts to encrypt all stored procedures of a database together.

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.

Encrypt all Stored Procedure in SQL Server, How to encrypt all existing stored procedures of a database i create stored procedures and now i want to hide my code or logic many preson  The only way I know is to script all the stored procedures and add the line "with encryption" I'd create a script for each stored procedure like this: IF OBJECT_ID('[dbo].[mySP]','P') IS NULL

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

Encrypting and Decrypting SQL Server Stored Procedures, Views , SQL Server stored procedures, views and functions are able to use the Is there a way to encrypt all stored procedures say per database? How To Encrypt All Existing Stored Procedure? Aug 18, 2006. I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION. But how about if I want encrypt existing stored procedures? Which command should I use ? View 4 Replies Similar Messages: How To Encrypt The Stored Procedure ? Encrypt A Stored Procedure. Encrypt Stored Procedure

How to Encrypt a Stored Procedure in SQL Server, To encrypt it, you add the WITH ENCRYPTION argument. You can also use the same argument to encrypt an existing procedure when using  Once encrypted, there is no straight forward way in SQL Server to decrypted the procedure. You may need to search the web to find third-party tools for decrypting it. So, keep the source code of the object in a source control before running the CREATE object script with encrypt option. Now, let’s see how to encrypt the stored procedures

Encrypting Stored Procedure, View & Function in SQL Server, You may come across situation where you have to hide or encrypt stored procedures, views and user defined functions in SQL Server. To encrypt the data in an existing, unencrypted database, you must create a backup image of the database, drop it, and then restore it into an encrypted database.

Encrypt a Column of Data, Azure SQL Database No Azure Synapse Analytics (SQL DW) No Parallel Data Warehouse. This article describes how to encrypt a column of  SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery. The contents are not able to be scripted using conventional means in SQL Server Management Studio; nor do the definitions appear in the definition column of sys.sql_modules.

Comments
  • We do something similar, but we also replace -- , ENCRYPTION which allows us to have additional WITH properties such as WITH SCHEMABINDING -- , ENCRYPTION
  • This is a really great snippet of code, thank you so much. Just getting into SMO and loving it. Thanks again for sharing.
  • There are two problems with this. 1) Parameters in the stored procedure go between the ALTER/CREATE and the WITH commands. 2) Long stored procedures may be stored in multiple rows.
  • Thank you for the information about SysComments table. I didn't know this.