Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
"I want to stop a user with a replicated copy of the database opening the database, modifying it and running queries against it." More specifically, what do you want the user to be able to do? I can modify my answer with specific permissions for you. –  Thomas Stringer Jun 6 '12 at 1:51
    
We haven't defined that exactly, but it would likely be one of two scenarios. Scenario #1 the user can log into the database and see some (or all tables), but then they will be read only. Scenario #2 is that the user cannot log in at all, or if they can they won't see any tables. –  peter Jun 6 '12 at 3:11
    
What would they be able to do in scenario #2 then? Sounds like you don't even want that user to be mapped to that database. –  Thomas Stringer Jun 6 '12 at 4:00

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
Sure I am aware of that, but SQL Azure or a web service is out of the question in my case. As a general option cloud services like this don't tend to be a great option in my opinion. –  peter Jun 26 '12 at 4:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.