You gave them dbo?

Quite often I hear  people say “I made them dbo” or “I gave them dbo” when announcing they granted someone full control of a database. It is quite rare in my experience that anyone would make a Server Login the owner of a database when aiming to grant a person uninhibited access to all things in a particular database. It is far more likely that when someone says “I made them dbo” that what they really meant to say was “I added them to the db_owner Fixed Database Role”, but admittedly “I made them dbo” rolls off the tongue a bit easier.

In the spirit of making proper technical distinctions wherever possible I want to identify three distinct database security concepts relevant in this context:

  1. The Database User named dbo that resides in every database and is mapped to the Server Login sa. The dbo User cannot be altered or dropped in any way meaning technically you “cannot make someone dbo” as that someone is already the sa Server Login and that cannot be changed.
  2. The Fixed Database Role named db_owner that resides in every database affords all members full control of the database including the ability to drop the database. The Database User dbo is a member of db_owner. Like the dbo Database User the db_owner Database Role cannot be altered except to add or remove members not named dbo. Additionally, the db_owner Database Role cannot be dropped.
  3. The owner of the database, i.e. the Database Owner. The Database Owner is the Server Login that is granted authorization, i.e. ownership, of a database. By default the Database Owner is initially set to the Login that created the database however this can later be changed using the ALTER AUTHORIZATION command. Like members of db_owner the Database Owner has full control of the database including the ability to drop the database. It is worth mentioning that the Server Login that owns a database is mapped to the dbo User when they connect to the database.

Membership in the db_owner Role is not the same as the dbo Database User which is not the same as being the Database Owner. These are three different concepts within SQL Server and it is important to make proper technical distinctions, especially when discussing database security.

The next time someone says “I made them dbo” ask them what they meant, exactly. It may become a learning experience for one or both of you. They may learn a thing or two if they have to ask you to clarify your question due to them not making the connection that there are three distinct concepts in play. At worst you will learn to decipher what the person you are working with actually carried out on the server whenever they said they “made someone dbo”, and those are both positive outcomes. In the best case scenario they will start to say something that describes what they actually did instead of the nonsensical statement “I made them dbo”.

About these ads
Tagged with: ,
Posted in Database Security, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: