Ways to stop access to a SQL express database

Question

I have a SQL express database and was wondering what options I have for securing it?

More specifically it is a replicated database using merge replication. I want to stop a user with a replicated copy of the database opening the database, modifying it and running queries against it.

UPDATE:

Just wondering though, what happens if I have a database which is secured, but I then take it offline, copy the database, and attach it to a different database server. Does the security go with it, and can I not break into it like that?

Answer 1

You need to utilize appropriate security principals. Create server logins and database users (and database roles for grouping permissions [and server roles if you are using SQL Server 2012]), and grant these principals the necessary permissions to do only what they need to do and nothing more.

Securing an instance/database is a very long topic, but a great place to start is Books Online. See this link at the bottom to view all the possible securables you can GRANT, DENY, and REVOKE permissions on.

You will most likely be concerned with Database Permissions with your described scenario (that doesn't mean you can ignore server permissions, but CONNECT may be all these aforementioned users need on the instance-level).

If all you want to do is allow the user to query data in a certain schema (for example), you'd simply do the following:

grant select
on schema::YourDbSchema
to YourDbUser

Note: It is best practice to create roles to house applicable permissions and then add users to roles instead of individual rights to users. It will become a management nightmare if you do the latter.

Answer 2

There is no way you can secure a SQL Express database against access from a computer administrator. In the typical SQL Express scenario this means that the user of your application (which is usually a also local administrator) can see everything in the database and it can also modify anything, no matter what grants or permissions you establish.

If you want to protect the IP in the database (which is the usual reasoning behind this ever repeating question) you need to prevent access to the physical database which you can only do by no releasing the database to start with. In other words, use a web service instead (hosted by you), or a cloud based database like SQL Azure.