Encrypt Stored procedures in Sql Server 2008

• zyxcba01 11808

  SSC Veteran

  Points: 272

  More actions

  January 10, 2011 at 5:22 am

  #232026

  We can use WITH ENCRYPTION option to create a stored procedure in encrypted format.

  But I need to send scripts for someone to run to create the encrypted stored procedures and I do not want them to see orginal text?

  Is there any way?

  Could you please help me...

  Thank you in Advance.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  January 10, 2011 at 5:33 am

  #1271326

  kind of... you can use this example to put together jsut a final script that executes a varbinary(max) ...so the proc body is not so obvious.

  just remember that SQL Server encryption is not foolproof. any determined DBA can read your procs if they decide they want to; all you are doing is prevening prying eyes from reading it, not a determined DBA.

  --totally obfuscate a command to send to the client:

  Declare @cmds Nvarchar(MAX)

  Declare @obfoo varbinary(MAX)

  -- use this code to build the not-so-readable string of the script

  Set @cmds = '

  PRINT ''This binary string will execute "SELECT * FROM SYS.OBJECTS":''

  SELECT * FROM SYS.OBJECTS

  '

  Set @obfoo = CAST(@cmds as varbinary(MAX))

  --copy and paste the resulting string to the final script

  Select @obfoo

  --this is the portion you'd send in a script to an end user.

  declare @_ as varbinary(max)

  set @_ =0x0D000A005000520049004E0054002000270054006800690073002000620069006E00610072007900200073007400720069006E0067002000770069006C006C002000650078006500630075007400650020002200530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A00450043005400530022003A0027000D000A00530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A0045004300540053000D000A00

  exec (@_)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• zyxcba01 11808

  SSC Veteran

  Points: 272

  More actions

  January 11, 2011 at 12:11 am

  #1271728

  Thank you Lowell.

  This is one option to hide the code little bit...

  Is there any other suggesstion from anybody?

• EdVassie

  SSC Guru

  Points: 60516

  More actions

  January 12, 2011 at 3:05 am

  #1272245

  Please be aware that stored procedure 'encryption' is just obfuscation. This feature was added to SQL Server way back when Microsoft could get away with mis-naming things far more easily.

  It is a simple matter to decrypt an encrypted stored procedure. Google 'sql stored procedure decrypt' for more information.

  Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
  

  When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

• qadirv

  Valued Member

  Points: 63

  More actions

  December 12, 2011 at 10:18 am

  #1420177

  Not an SQL solution, but it does the job of encryprting all the procedures.

  http://www.codeproject.com/Tips/239100/Encrypting-all-the-Stored-Procedures-of-a-Database