Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I protect my SQL Server Database code

Posted on 2009-02-21
9
Medium Priority
?
238 Views
Last Modified: 2012-06-27
How do I protect SQL Server 2005 database in a way that nobody can view table structure or stored procedure without using "WITH ENCRYPTION" or "SQL CLR" technique
0
Comment
Question by:yatin_81
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 9

Expert Comment

by:Ken Fayal
ID: 23701546
You would do this with the proper use of user permissions.  Is it something you deploy to customers, where you don't have control over the server?
0
 
LVL 2

Author Comment

by:yatin_81
ID: 23701678
Basically, the whole database would be lying on client's server. It would be possible for them to use Windows Authentication and Management Studio to connect and view database components. I need to avoid this. One solution I found was using SQL CLR by creating a Database Project in VS2005. But the database is such huge, it wont be easy task. Any more suggestion would be appreciated....
0
 
LVL 22

Expert Comment

by:dportas
ID: 23703846
WITH ENCRYPTION will protect your code from casual users but it won't completely secure it from a user with Admin access. Nor will CLR.

The best way to protect your intellectual property is with a Licence Agreement. In my opinion you shouldn't try to hide procs from admins even if you can. It's part of their job to know what is running on their server (unless you are providing a DBA service as well).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23705336
You can't and should not even attempt to do so. As dportas has indicated you need to rely on EULA's.
0
 
LVL 2

Author Comment

by:yatin_81
ID: 23709066
All your answers are appreciated. But I need to clarify my situation.
All the task related to Database Administration will be done by my team only. Its a fear of our management that our code in the form of stored procedures and the logic applied will be visible to the clients. The chances may be that client might take the database to our competitor, and that would be harmful to the business.
Please suggest how to avoid these situation.
0
 
LVL 22

Accepted Solution

by:
dportas earned 500 total points
ID: 23712643
The user can't see procedure definitions as long as you deny or don't grant the "VIEW DEFINITION" permission on the procs. This permission is normally denied by default unless the user owns the proc or has the ddl_admin role.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23715236
>>All the task related to Database Administration will be done by my team only<<
But they own the data and probably the server it runs on and there is simply nothing you can do to prevent them from viewing the stored procedures if they really wanted to.

>>The chances may be that client might take the database to our competitor, and that would be harmful to the business.<<
IMHO You have 2 choices:
1. Make the EULA rock solid.  i.e. Check with your lawyer.
2. Don't sell them the software.
0
 
LVL 2

Author Closing Comment

by:yatin_81
ID: 31549638
Exaclty not looking for but can have to close the questions. Amongst all the answers this one was pretty straight forward near to my question
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

810 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