deny access to SQL2005 database, only through Program

Question

How can I deny direct access to my SQL2005 database structure by anyone including a SA, when I deploy the database with my VB program to Users? The deployment is using SQLexpress which can/might be created with a System Admin signon. Is it possible to keep the DB structure (intellectual property) private from other prying eyes? I only want controlled acces through the VB program. Help!

Answer 1

No , you cannot have such a security structure for your sqlserver .
By defualt SQL sysamdin will have access to all the databases in that sql server , irrespective of whether they are added explicitly to that db or not.
SA account will be sysadmin in a sql server (unless it is disabled) .
Your requirement is acheived only when sa is disabled , there are no other sysadmin account is there in your sql machine and you only have DB_owner role in the database.

---

Thanks, Leks

Answer 2

Thanks Leks, If the SA is disabled in the sql server, with no other SA and I am the DB_Owner, can anyone install an instance of sql on another machine, enable the SA and attach the server to a copy of my .mdf file, will they be able to then access the restricted DB? 

Answer 3

The mdf and ldf  files can be copied only if sql server is stopped. Make sure you dont give full access to your mdf and ldf files on the file system on your machine , so they cannot copy and subsequently attach the files even is your sql server is stopped.

---

Thanks, Leks

Answer 4

To reinforce and clarify the points Leks makes.

The 'sysadmin' (sa) account 'owns' the server instance. If any user on a server instance is a member of the sysadmin account, they will have complete access to any database on that server instance, and you cannot prevent such access.

If you were to use the option to deploy a complete 'instance' instead of a database, the end user only had to stop the instance, copy the database files to an instance where he/she is the sysadmin, and they will then have complete access to your database.

UNLESS, you employ database encryption. (Which, unfortunately, is NOT a SQL Express option.)

---

You may be only one person in the world, but you may also be the world to one person.

Answer 5

Thanks Arnie, That gives more clarity