|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:13 AM
Points: 6,
Visits: 76
|
|
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.
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 11,605,
Visits: 27,646
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:13 AM
Points: 6,
Visits: 76
|
|
Thank you Lowell. This is one option to hide the code little bit...
Is there any other suggesstion from anybody?
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:57 AM
Points: 2,619,
Visits: 2,749
|
|
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.
Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads. Disclaimer: All information provided is a personal opinion that may not match reality. Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, December 12, 2011 10:17 AM
Points: 1,
Visits: 0
|
|
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
|
|
|
|