SQL Encryption
So, you have painstakingly written a very complex view or stored procedure. Hours of work went into that job.
Now it has to go into the database of the customer.
From there on, everyone who has access to that database can see your work.
And even worse, they can copy and re-use it in a fraction of the time you spent on it.
Ah, if only there was a way to make that impossible.
Well, there is a way, actually. Here's how you can do that!
Why you would use Encryption
The first reason is obvious. You don't want others to copy your work in seconds where you spent hours, maybe even days of work.
But there is another reason. Encryption prevents others from making changes.
Yeah, maybe that view or stored procedure wasn't all that difficult to write.
But it is very important to maintain data integrity.
To prevent others from changing your work, encryption might also be the correct answer.
How to use Encryption
To encrypt your view, you can use:
/* Your query for the view */
GO
Similar, you can use encryption for your procedure:
/* Your query for the procedure */
GO
What this does
At first glance, encrypted views or procedures work exactly the same as regular views or procedures.
If you would do a select on your view, the results will be shown almost identical as if it as a non encrypted view.
But if you look closely, you will notice that you can not go to the execution plan.
This is because the execution plan would show what is happening in the background and the user might be able to determine the business logic you used.
Encryption prevents this, so sorry. No execution plan here.
When you go to the object explorer, you will notice you cannot modify these encrypted objects.
Also, if you go to "Tasks, Script Object as Alter to" will not be clickable.
If you are more experienced and thought using "exec sp_helptext ..." would do the trick.
Sorry, that one will only give you a message that the object is encrypted.
A little big caution here
When you encrypt your view or procedure, nobody can see what's inside it.
And nobody includes you as well.
So always be sure to have a non-encrypted version in your development database.
Or save the SQL statement in a save place.
Please be very aware of this. Otherwise you would have to rewrite it all over again if you need to make changes or need it elsewhere.
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!