How to secure our MSSQL 2008 Express database ?

Question

Post reply

How to secure our MSSQL 2008 Express database ?

Dominic Gagné

SSCrazy

Points: 2654
More actions
September 9, 2010 at 8:45 am

#387756

Hi,
We have a production control software design in .NET and it uses MSSQL 2008 Express database installed locally on the industrial computer (which is a complete system with I/O boards, PLC and industrial grades parts).
I would like to secure our database to not let anyone with administrative right on the computer to access the DB. The computer is the client's property but our software is not. Tables and SP's should not be accessed by anyone else. I know I could encrypt the SP, but we do remote maintenance on a regular basis and need to access the SP code.
Is there a way to secure our database and denied any Windows administrator to access so only SQL users defines in the DB can log into it?
thanks for your precious time and help

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

create a role/s and define required permission to that role.

Then you can add users in that role.

It will be easy for you to manage.

----------
Ashish

Dominic Gagné SSCrazy Points: 2654 More actions · Answer 2

thanks for the reply.

let's say I have a single user (SQL Server Login user) that needs to log in the DB and everyone else, including Windows administrator, shouldn't, what I need to do in the role??!

I never configured MSSQL security before, so sorry if it seems stupid.

thanks

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

By default, windows administrators are not sysadmins or not even members of Public role SQL Server. You will have to explicitly add a Windows authentication user. You could add just your Windows user into the DB and make yourself sysadmin. That should do the trick.

But double check on your test environment.

-Roy

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 4

Note that with physical access, a Windows admin could find a way in. If this is just to prevent issues from Windows admins making changes, go the administrative route, and have management issue a note to Windows admins to stay out of SQL Server.