SQL server

Protecting your code.

There was a query that I spent two weeks building and tuning. I had to figure out the business logic and do a lot of data discovery (SQL profiler). At then end I came out with some neat little code that was able to do somewhat of an ETL process between two of our Major HR DBs. This prevented the IT group from having to input data into two different systems. Anyways, due to our security policy, there were support staff that were poking around our SQL servers. They had figured out how to right-click in SSMS and see the body of a stored procedure. That is when I wanted to find out a way to prevent casual surfers from poking into code, and then coming back with questions.

The simple step I took, was to make stored procs of all our text queries, and then encrypt them. This is easy, and the code is not viewable from SSMS or by using sp_helptext. Throwing sp_password as a comment will further hide the code from being seen in the SQL profiler.  Make sure you have a text copy of the proc stored safely. The code is as follows:

CREATE PROCEDURE proc_name
WITH ENCRYPTION
AS
BEGIN
SELECT * from intellectul_table;
END

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s