Want to ecrypt all your stored procedures with one click?

  • Article

I was working in a small project in which I should deploy the database on the production server with Encrypted Stored Procedures. I had to open each and every stored procedure in the database to add WITH ENCRYPTION keyword to them to be encrypted. I thought I can avoid this pain by write a simple tool that do this work. I wrote it using VB.NET.

To give overview about this tool, first we should consider that

  • SQL Server saves all stored procedures' text in any database in a table called sysComments exists in any SQL Server database.
  • When you encrypt a stored procedure, there's no way to decrypt it back to its original text so BE CAREFUL and always encrypt a copy of your development database

This windows application takes two arguments from the user, server name and database name. It assumes that you are using integrated authentication with SQL Server as you would do that in development environment.

I use this information to access the database and query the sysobjects system table to get the IDs of the user stored procedures and then query the sysComments system table to get the text of each stored procedure and modify it to add WITH ENCRYPTION keyword.

The best thing in this tool that it shows you which stored procedures that have been altered and which have not. It shows this report in a datagrid and you can save this report as XML file if you want for your convenience.

I added the binary of this tool in this site and if you have time to take a look at the source code, it's here

Have fun :)

--Mohamed