How to lock out everyone but me?

I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTIN\Administrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.

I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTIN\Administrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTIN\Administrators' because it does not exist or you do not have permission."

What I can (apparently) successfully do is run DENY CONTROL TO [BUILTIN\Administrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:

sqlcmd -S (local)\MyInstance -d MyDB -Q "select * from my_table"

and see the contents of my_table.

What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?

Have you tried simply dropping the builtin\administrators login?

"Drop login [builtin\administrators]"

You can check sys.server_principals for a list of all logins. This seemed to work for me, although I tested this on Developer Edition and not Express Edition.

Sung

|||

I think the right expression would be to deny connection to builtin administrator under normal circumstances.

I want to clarify that actually stopping a box administrator from accessing SQL Server instance (and any other service for that matter) is practically impossible as your adversary has control over the OS and most likely the hardware.

You need to trust the box admin to some extent, and a proper channel and policies to monitor unauthorized activities (i.e. company policies, box auditing, etc.) should be in place to keep a box administrator honest. If you are hosting any data on a machine where you don’t trust the box administrator, make sure you don’t store (and I would even say don’t manipulate) any valuable information in clear text on the system you don’t trust.

If your objective is to keep an honest box administrator honest and deter her from accessing SQL Server, revoking access as Sung mentioned should be enough. In SQL Express you additionally would have to drop the [Builtin\users] account. SQL Express is designed OOB to grant access to the instance (with low privileges) to any user in the system in order to take advantage of some SQL Express-specific features.

BTW. Keep in mind that in SQL Server 2005 it is sufficient to start the server in single user mode. The reason for this is to allow a box admin to recover the system in case of emergency.

I hope this information will be useful.

-Raul Garcia

SDE/T

SQL Server Engine