Solved

How to protect store procedure

Posted on 2009-04-04
13
207 Views
Last Modified: 2012-05-06
How i can protect store procedure from shared server. or i can make dll files of store procedure and used with asp.net
0
Comment
Question by:snbelani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
13 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 334 total points
ID: 24068142
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
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 166 total points
ID: 24068241
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24068636
Experts
For the record the author has asked 7 questions and has yet to close a single one.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24068642
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
 

Author Comment

by:snbelani
ID: 24088975
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24089328
As I mentioned earlier, add the WITH ENCRYPTION option while creating the stored procedure.
0
 

Author Comment

by:snbelani
ID: 24569170
If i create sp with encryption, can i create sql query with data publishing wizard?
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 334 total points
ID: 24569560
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
 

Author Comment

by:snbelani
ID: 24569977
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24569987
>>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
 

Author Comment

by:snbelani
ID: 24583152
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24588128
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 how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question