How can i Encrypt Stored Procedure in SQL [Resolved]

Posted by Patnaik.Jyoti under Sql Server on 3/15/2012 | Points: 10 | Views : 1928 | Status : [Member] | Replies : 14
I want to keep my store my stored procedure in Encrypted form.




Responses

Posted by: Sksamantaray on: 3/15/2012 [Member] Silver | Points: 50

Up
0
Down

Resolved
Hi,

Create procedure ProcedureName with encryption

as
Begin
select top 5 * from customers
End


Thanks,
Sanjay

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/15/2012 [Member] Silver | Points: 25

Up
0
Down
Hi,
You can use with encryption keyword while creating a procedure

Thanks,
Sanjay

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patnaik.Jyoti on: 3/15/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Sanjay,
Can you add a Snippet?

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patnaik.Jyoti on: 3/15/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks,
its working for me

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/15/2012 [Member] Silver | Points: 25

Up
0
Down
Hi Patnaik_Jyoti,
If it works for you , then click Mark As Answer Section.

Thanks,
Sanjay

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi Sanjay,
great tip indeed but if i encrypt the stored procedure then how will i edit it if in case i need it ?


Thanks and Regards
Akiii

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/17/2012 [Member] Silver | Points: 25

Up
0
Down

You have to keep the source /backup before you encrypt put it in production.
Otherwise you have to purchase 3rd party software to decrypt the procedure.

Thanks,
Sanjay

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2012 [Member] Bronze | Points: 25

Up
0
Down
Isn't there any way in SQL server itself to decrypt the stored procedure ?


Thanks and Regards
Akiii

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patnaik.Jyoti on: 3/17/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Akkiii,
I have gone through lot of forums and articles,every one say sql does not allow to decrypt the encrypted store procedure.

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/17/2012 [Member] Silver | Points: 25

Up
0
Down
Hi Akiii,
Sql does not allow to decrypt encrypted storedprocedure.
This tells more about this:
http://blog.sqlauthority.com/2007/07/01/sql-server-explanation-of-with-encryption-clause-for-stored-procedure-and-user-defined-functions/
http://www.mssqltips.com/sqlservertip/1046/decrypting-sql-server-database-objects/

Thanks,
Sanjay

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/18/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi Jyoti,

There should be a decrypt option in sql server. Don't you think so ?
I hope that Microsoft find fixes that thing !


Thanks and Regards
Akiii

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/18/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi Sanjay,

Thank you the links !

But do post them in links tag, it would look better !

Regards
Akiii


Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kamalakanta.Nayak09 on: 3/20/2012 [Member] Starter | Points: 25

Up
0
Down
How to encrypt the procedure after creation of procedure in sql server 2008?

Thanks,
K.K

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/20/2012 [Member] Silver | Points: 25

Up
0
Down
Hi Kamalakant,
If it is already created then try this:(But keep a backup of original)
alter procedure ProcedureName with encryption

as
Begin
select top 5 * from customers
End


Thanks,
Sanjay

Patnaik.Jyoti, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response