Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Encrypt Stored procedures in Sql Server 2008 Expand / Collapse
Author
Message
Posted Monday, January 10, 2011 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 6, 2015 5:22 AM
Points: 6, Visits: 93
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.


Post #1045221
Posted Monday, January 10, 2011 5:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 14,469, Visits: 38,076
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!
Post #1045227
Posted Tuesday, January 11, 2011 12:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 6, 2015 5:22 AM
Points: 6, Visits: 93
Thank you Lowell.
This is one option to hide the code little bit...

Is there any other suggesstion from anybody?
Post #1045711
Posted Wednesday, January 12, 2011 3:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 30, 2016 2:28 AM
Points: 3,034, Visits: 3,711
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2016, 2014, 2012, 2008 R2, 2008 and 2005. 29 Aug 2016: now over 38,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "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
Post #1046344
Posted Monday, December 12, 2011 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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


Post #1220373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse