Marius Lardizabal – IT Consulting

Yesterday I had a very peculiar experience with a stored procedure in SQL Server 2k5.

I was trying to debug an SP from a 3rd party vendor for my client when I noticed I couldn’t modify nor script the dang thing.

I was so frustrated I sent out a distress call to my developer pals. And luckily Jonathan gave me a hint.

One little known option called WITH ENCRYPTION can be added when creating a stored procedore to ensure that that stored procedure could not be viewed when deployed. The only caveat once encrypted there’s no way to decrypt (or so they say). Instead you have to make sure to save the script you used into a file and perhaps put it under source control say Subversion.

the code looks like this

CREATE procedure [dbo].[Ten Most Expensive Products Encyrpt]
WITH ENCRYPTION AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

Snippet Compliments to Suprotim Agarwal of SQL Server Curry Anyhow there are several ways to see ‘hack it’:

- if you’re only concerned on what objects the stored procedure will access you can checkout the dependency list (I was able to inject a couple of dummy rows just by reading the tables listed)

- you can run the profiler and watch statements run (the only downside you won’t be able to know if it was run under an IF-THEN-ELSE statement)

- or you can attach a debugger to the SQL Server Process and get the decrypted stored procedure in memory.

Anyhow, its simply amazing, I’ve been working with SQL Server for almost 11 yrs and yet everyday I learn something new.

Due to the global financial crisis efforts to progress our beloved Firebird database is in great danger of being ruined.

A call for asistance has been placed by the foundation and as a proud member I am imploring you to donate as much as you can to the cause.

You can click on the Paypal image below

or

you can goto to this link for more details on contributing to the Firebird Foundation.