SQLEXPRESS DB security and logistics

Question

hello,


I have a project in which im using SQLEXPRESS. i need help and guidance in below mentioned points


1. How to lock the SQL DB when the installation of the software is done so that the user does not tamper with the DB.
2. how to put a SA login and password still keeping the path to anything and not fixed


currently, i need help on the above, if there is anything else i shall ask


thanks and regards
hari vaag

---

HV

Answer 1

You cannot lock the database from members of sysadmin role. What do you mean by " how to put a SA login and password still keeping the path to anything and not fixed"?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

If you are installing the database in a corporate environment where the users are not administrators, it is not very difficult. You have SQL Server running under a dedicated domain account which has permission to the SQL Server installation, and set file permissions so that the user cannot access the files. The presumption is that you are part of corporation yourself.

But if the user is administrator on the machine, there is no way you can prevent the user from accessing the files and do whatever he likes with them.

If all you want do is to save the user from being too smart for himself, and keep him out from connecting from Excel or whatever, you could run the installation as Local System, and be sure that you don't add the user to the sysadmin role. You would also have to design the application so that the user still can work.

If you really want to keep the user out to protect intellectual property, you will need to consider a hosted solution, be that SQL Azure or some other hosting provider.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

what i meant was, when i deploy the app at clients place, SQL DB will be installed, but want to make it secure so that no one is able to open the DB and so on... Hope im clear... thanks

---

HV

Answer 4

Hi hari,

Do you mean that you installed the SQL DB in clients server? I don't think it is a secure deployment.

In this scenario, I'm afraid that we can only deal with the windows account. You need to create a new windows account for the users, and the account couldn't be the administrator in your client machine, also do not grant any permissions to that account in SQL Server.

Storing the database in another server is more secure. For more information, you can refer to:

http://msdn.microsoft.com/en-us/library/aa175395(v=sql.80).aspx

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.