• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

How to protect store procedure

How i can protect store procedure from shared server. or i can make dll files of store procedure and used with asp.net
0
snbelani
Asked:
snbelani
  • 4
  • 4
  • 2
  • +1
3 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Use the Create Procedure "WITH ENCRYPTION" option to avoid others seeing the code of your procedure.
No need for converting it into CLR stored procedure using DLL files.
0
 
RiteshShahCommented:
WITH ENCRYPTION option is pretty cool in your scenario I guess. As long as possible, try to close to data for better performance rather than being closer to application. I agree with rriegan17, if it is possible, use SP in SQL Server only.
0
 
Anthony PerkinsCommented:
Experts
For the record the author has asked 7 questions and has yet to close a single one.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
And incidentally, the only question that was not deleted (it was Force-Accepted) was identical to this question.  See for yourself:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23854625.html
0
 
snbelaniAuthor Commented:
I can not upload Store procedure from my end i have to deliver complied apllication along with Script for database which i generate from data publishing wizard so how i can encrypt store procedure?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
As I mentioned earlier, add the WITH ENCRYPTION option while creating the stored procedure.
0
 
snbelaniAuthor Commented:
If i create sp with encryption, can i create sql query with data publishing wizard?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you use WITH ENCRYPTION, you wont be able to create the Script using Data Publishing Wizard.

If you wish to do that, Create the procedure and grant only Execute privilege to those who should not view the procedure code.
Say if you have a procedure named dbo.temp_proc and you have create procedure privileges over that db, then
1. create that procedure and you will be having full privileges over that procedure.
2. you will be able to execute and script that procedure either directly or through Data Publishing Wizard.

Now if we have an user named test and he needs only execute privileges and no scripting privilege, then issue the statement below:

GRANT EXECUTE ON dbo.temp_proc TO test;

Now test can only execute the procedure and he wont be able to view the procedure code or even through Data Publishing Wizard.

Hope this helps.
0
 
snbelaniAuthor Commented:
My problem is,  in my web application i had near about 800 store procedures and i had to give to my client how i shall give sp in query form with encryption
0
 
RiteshShahCommented:
>>My problem is,  in my web application i had near about 800 store procedures and i had to give to my client how i shall give sp in query form with encryption<<

do you want to encrypt all 800 SPs? or set rights as per rrjegan17 to 800 SPs?
0
 
snbelaniAuthor Commented:
Yes, I do with encrytion with every store procedure after final testing.Now how should i deliver encrypt sp to client so that he can upload from his end
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You need to deliver the procedure code using WITH ENCRYPTION option to your client and can be installed out there.
To create the procedure you can't do that with Encrypted one.
But while Creating the Procedure, you can prevent from others viewing the code later, you can use WITH ENCRYPTION.

Hope this helps.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now