1

We have SQL Server Express 2008 on a production server. For the database in question we need to ensure that the local development version is identically configured to that on the production server.

In the production db the User dbo has a blank login.

In the local db the User dbo is using the login of the local machine's administrator.

I can change the dbo to be a different login (say sa) on both machines, but I don't really want to mess with the production setup.

So, 2 questions:

  1. Are there legitimate (e.g. security) reasons to have a db's dbo user with an empty login, or is it poor/incomplete setup?

  2. If there is nothing wrong with it, how can I remove the login from the local database's dbo user?

Thanks for all guidance!

Colin Ross
  • 589
  • 2
  • 12
Serexx
  • 1,232
  • 1
  • 15
  • 32

1 Answers1

1

The database user dbo is unique, as in logins in the sysadmin fixed server role are automatically mapped to it. That's probably what you mean by "blank login".

Are you using the sa login or another login with sysadmin privileges to access the database?

  • Thanks for your response - the db is accessed by Asp.Net apps which use a separate User/Login pair for access - that User has a default schema of 'dbo' while the Login has Server Roles of 'processadmin' and 'public' - – Serexx Feb 01 '12 at 21:07
  • also, as far as I can see, the dbo User has a single Role of 'db_owner' – Serexx Feb 01 '12 at 21:12
  • @Serexx Yes, `dbo` has the **database role** of `db_owner`. The login, an instance level object, that uses `dbo` are logins with `sysadmin` privileges. Remember, logins and database users are two separate entities. –  Feb 01 '12 at 21:48