Encrypt Objects

Transact-SQL
1

Hi All,

I want to encrypt all my database objects like Procedures and functions for QA and Prod Server.
Is there any way we can encrypt all objects in one go.

Thanks In Advance.

2

Why? They are easy to decrypt.

If you are concerned about people altering code then write a routine to compare the code in the DB against the latest code in source control.

3

This is the required to encrypt all stored procedures in QA/Prod. That's why I need to do.
Please share the way to do that to encrpt all objects in one go.

4

You say this is required - but why is it required and what level of encryption is needed? If the requirement is to encrypt just the code then the only way to do that is by adding WITH ENCRYPTION to each procedure, function and view.

If the requirement is to enable encryption for the whole database - then you should be looking at TDE and setting that up.

5

Thanks.

I want the script which can add encryption in all stored procedure (WITH ENCRYPTION) . I am not able to do that . I have around 1000 procedures and it will take time if I will add manually one by one in all stored procedure. Also same activity applicable on production and I don't have access of production server.

6

There is no script to do what you want - and if this is required then you need to get started and modify every procedure manually.

I would not trust any manual script to do this either - you need to insure that you keep a good unencrypted copy of the code (ideally in a code repository) before you encrypt anything. Once encrypted - if you don't have the original source code it will become much more difficult to manage.

1 Like
7

look at the following systm view

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [ROUTINE_NAME]
      ,[ROUTINE_TYPE]
      ,[ROUTINE_DEFINITION]
      ,replace([ROUTINE_DEFINITION],'create proc','alter proc') as ALTERPROC
  FROM [INFORMATION_SCHEMA].[ROUTINES]
  where ROUTINE_TYPE = 'PROCEDURE'

I will let you run with it. Test it in dev/sqa first though. Not sure if it will work, or is even recommended. but worth looking into for changing n procedures.
Or you can extract all of your stored procedures then maybe using some scripting language like powershell you can modify its content. Or use tools like RedGate etc

8

another way is

SELECT OBJECT_DEFINITION (OBJECT_ID(name))
from sys.procedures

then you can use this to generate ALTER PRocedure with encryption type of thing.

9

Thanks for your help.

Is there any tested script which I can use to encrypt all stored procedure. Because I can't take the risk to do the same in production.

10

What do you mean "tested?" What level of test would you feel comfortable deploying in your prod? You would not want anyone else's "tested script" run in your prod environment. Even if it was provided to you from Microsoft or Bill Gates himself. You have to test it in your pre-production environment 1st and test it thoroughly anyways.
Do you not have a preprod environment you can test it in?

11

Sure I will test myself. But I need the code which resolves my problem. I didn't get any code that's why I am asking. Please provide the code for this. it will very helpful for me.

Thanks In Advance.

12

I have never seen one single script that can do what you want. I think of it more of a process of steps that you need to take to get a script that will do what you want.

we can list out the steps and then you can use those steps to arrive at the script you want.

  1. create 2 folder
  • SprocsWithoutEncrytion
  • SprocsWithEncryption
  1. In SSMS run the following script
SELECT [ROUTINE_NAME]
      ,[ROUTINE_TYPE]
      ,[ROUTINE_DEFINITION]
,replace([ROUTINE_DEFINITION],'create proc','alter proc') as ALTERPROC
  FROM [INFORMATION_SCHEMA].[ROUTINES]
  where ROUTINE_TYPE = 'PROCEDURE'

Highlight the ALTERPROC column, copy and paste into a file in the folder SprocsWithoutEncrytion
Or as another option use ssms to script out sprocs only into one folder either as one or one file per sproc. DO NOT USE CREATE & DROP, just ALTER. As Create drop you also lose permissions on the sprocs

  1. Now to the part that will have the WITH ENCRYPTION. This part will be a bit tricky as we do not know the pattern that were used to create the sprocs. Usually I use
create proc xyz
as
begin
---Code bits
end

So do 1 & 2, and please respond once you have that here. and show us a screen capture of what you have done.

13

When I copied the "ALTERPROC" Column Data. Its Like that . Please find below Sample :

ALTER PROCEDURE [dbo].[Proc1]
(
@ColumnName VARCHAR(200)
,@TableName VARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ON
/*Bussiness Logic */
END

ALTER PROCEDURE [dbo].[Proc2]
(
@Mode BIT = 0
,@CustomerId INT = NULL
,@UtilityId SMALLINT= 0
,@TimeOffset INT = 0
)
AS
DECLARE @LoginAcctLockAttempt SMALLINT
,@LoginAcctLockDuration TINYINT
,@UtilityAccountNumber INT
BEGIN

BEGIN TRY
			 /*Bussiness Logic*/
END TRY
BEGIN CATCH
			 /*Execption Handling */
END CATCH

END

14

waiting for your response.

15

I was going to try to see if you could do a REPLACE on AS
BEGIN but your sprocs do not use that consistently. let me try something else

16

Thank you very much.

17

This will get you started. It will need some clean up on your part. Procs might differ in content from one dev to another so you will have to account for some anomalies to get what you want.

  SELECT REPLACE(OBJECT_DEFINITION (OBJECT_ID(sp.name)),'create proc','alter proc') PROC_SIN_ENCRYPTION,
         replace(replace(REPLACE(REPLACE(OBJECT_DEFINITION (OBJECT_ID(sp.name)), CHAR(13), ' '), CHAR(10), ''),'create proc','alter proc'), ') as', ' )  WITH ENCRYPTION as') PROC_CON_ENCRYPTION
    from sys.procedures sp
18

this is fine but it also change the format of stored procedure. previous and new one is completely different. so, I am not able to check code difference between both procedures.

19

Hi

There are formatting software available ONLINE

Example: this formats any SQL ..
http://www.dpriver.com/pp/sqlformat.htm

Please look for
Stored Procedures SQL Server formatting
ONLINE

Also
there are people who have scripted
formatting stored procedures and these scripts are
available ONLINE .. please search for them

Need any help PING me

Thanks

1 Like
20

you are welcome.