Lock out non-dbo

Question

Is there an easy way to lock a sql server express 2005 so that only DBOs can get to it, assuming you have a system where everyone has been granted rights individually and you can't just disable a role?

Answer 1

You can remove all other access than dbo from the database, then only the dbo's will be able to use it.

However, members of the sysadmin group are by default dbo's, I am not sure if you can block access for these users.

Answer 2

Is there not a server wide setting for DBO-Only? I do not have an example close to hand, but from my Sybase days I seem to remember such a setting.

Answer 3

Restricted user mode should do it. Granted it will also let in dbcreator and sysadmin, but that only makes sense. So ensure your accounts don't have one of those roles either or they will be able to get in when in Restricted User Mode.

http://technet.microsoft.com/en-us/library/ms188124.aspx

Restrict Access

Specify which users may access the database. Possible values are:

    * Multiple
      The normal state for a production database, allows multiple users to access the database at once.
    * Single
      Used for maintenance actions, only one user is allowed to access the database at once.
    * Restricted
      Only members of the db_owner, dbcreator, or sysadmin roles can use the database.

Answer 4

ALTER DATABASE <dbname> SET RESTRICTED_USER

and to set operation back to normal:

ALTER DATABASE <dbname> SET MULTI_USER