Encrypt Userdefined Stored Procedure

Question

Need script/procedure/step to Encrypt all Userdefined Stored Procedure's in a d.b

Thanks

---

Naveen [MCTS] | Press Yes if the post is useful.

Answer 1

Hi ,

Using the WITH ENCRYPTION clause stores the procedure definition in an unreadable form. Once it is encrypted is encrypted, it cannot be decrypted nor viewed by anyone, including the owner of the stored procedure and the system administrator.  In case if you tend to use this , make a clear doc and copy of SP stored somewhere else for future reference.

CREATE PROCEDURE encrypt_test



WITH ENCRYPTION



AS



SELECT * 



FROM test



GO

---

Thanks, Leks

Answer 2

Hi Leks,

Thanks for the reply.

Is there any way to encrypt all the user defined stored procedure at a time instead of opening the SP one by one and encrypt it.


Bascially i need to ensure that all the user defined sp's stored are encrypted before i give them development team.


thanks

---

Naveen [MCTS] | Press Yes if the post is useful.

Answer 3

I am not aware of any such options and dont think any such options are availabe in sql server .
May be you can sort this out by the level of access you give to your Dev team instead of looking at encrypting the contents in all Sps at the same time.

---

Thanks, Leks

Answer 4

Actually, "WITH ENCRYPTION" is an old clause that is only providing some simple obfuscation and should not be used in any scenario where the word security is also used. It can be easily broken to retrieve the original stored procedure code. Feel free to use it, but don't expect it to protect you from database administrators and don't expect it to work as some kind of DRM mechanism for protecting code you are shipping with a database. The only reason this option is still available is for backward compatibility. To my knowledge there are no plans to improve it and no immediate plans to remove it either.

Again, DO NOT USE "WITH ENCRYPTION" if your use is due to any security concerns!

---

This post is provided "AS IS" with no warranties, and confers no rights.