Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query notifications were failing.

Supposedly the best practice for dealing with this is to make 'sa' the database owner. We changed it and that cleared out the queue.

My (very elementary) question: what is the database owner and what is its purpose?

share|improve this question
    
How did you go about changing the database owner to 'sa'? I am a GIS Tech working for local government. The old GIS Tech was fired and very few people around here know much about GIS. I can't seem to give myself permission to edit a database because I'm not the owner. How do I change ownership? –  user25879 Jul 11 '13 at 15:07

2 Answers 2

up vote 15 down vote accepted

There is some confusion out there between the database concepts of the 'dbo' (a user) and 'db_owner' (a fixed role) on one side and the instance concept of 'database owner' on the other side. The 'dbo' and 'db_owner' are often called 'database owner'. In what you're asking you are talking about the database owner as the server principal that owns the database.

The theory goes like this: anything that can be granted permissions on is a 'securable'. All securables have an owner. The owner of a securable has absolute control over the securable and cannot be denied any priviledge. Instance level securables are owned by server principals (logins). Database level securables are owned by database principals (users). Principal come in two flavor: primary (identity) and secondary (membership). Server level securables are by default owned by the currently logged primary server principal. Database level securables are owned by default by the current database principal, except for schema bound objects that by default are owned by the schema owner. All securables support the AUTHORIZATION clause at create time to enforce a different owner. ALTER AUTHORIZATION can be later used to change the owner of any securable.

Therefore hence the database is a server level securable it follows that it will be, by default, owned by the primary principal that issued the CREATE DATABASE statement. Ie. the NT login of the departed employee.

So your question is really "Why do securables need an owner?". Because the owner is the root of trust. It is the owner that grants, denies and revokes permission on the object. Can a security system be designed without owners of securables? Probably yes, but there would have to be some mechanism in place to replace the role owners play in the current model. For instance consider that dad securables have no owner (eg. instead of owning a securable, the original creator is just granted CONTROL over it) it would be possible create a securable and revoke access on it to everybody, including himself. The requirement of an owner circumvents this problem since an owner cannot lock himself out.

The little known side effect of CREATE DATABASE of creating a securable (the database) owned by original NT login has burned many before. The rules are the same for every securable, but some factors aggravate the DATABASE owner issues:

  • the other server level securables (endpoint, server role, login) are far seldom used, moved around etc.
  • database level securables usually end up by being owned by dbo (the database principal), or some other database principal, and thus the owner is contained with the database
  • Having the database ownership default to the NT primary principal craetes a containment issue (the owner is an NT SID managed by AD and does not travel with the database files, the NT account can be thumbstoned etc etc etc)
  • the most important thing: the database owner has important side effects, specifically the EXECUTE AS context. This later problem is what burns most users. Since Service Broker makes extensive use of EXECUTE AS (the message delivery has an implicit EXECUTE AS context, as well as queue activation that has an explicit one) is usually Service Broker users that discover this problem first.

BTW, Kudos for investigating and fixing your original problem :)

share|improve this answer

The database owner is a bit of a throw back to a time before (proper) schema's were introduced in SQL Sever 2005.

Basically a database owner is the default dbo (database owner) of the database, with the database itself being a database object.

From the SQL Server 2000 docs ...

The dbo is a user that has implied permissions to perform all activities in the database.

In earlier versions of SQL Server, when a schema could not "own" a object (or rather it should be stated that all the objects, tables, views, etc. were owned by dbo and there were no other schemas) it was necessary for a "user" to own it ... it should go without saying why something needs to own the database (or else permissions in general would be rather difficult.)

So, technically in older versions of SQL Server (or upgraded databases) it wasn't the "Foo" table it was the "dbo.Foo" table ... with the dbo being the owner.

With the advent of SQL Server 2005 you could have schema owned database objects like say you have a schema named "bar" and table named "Foo" ... this becomes bar.Foo as in ...

SELECT * FROM bar.Foo WHERE etc = 'blah`;

The tricky part comes it with the fact that the user creating the database is automatically set as the owner which leads to issues with employee turn over, etc.

Therefore is it best practice to either change this to the sa account, or perhaps (in my experience) to a domain account that can be administered by an organization's ops/IT team.

This article gives a break down the difference between the older "owner" way of doing things, and the newer "schema" based ownership system.

To understand the difference between owners and schema, let’s spend some time reviewing object ownership. When an object is created in SQL Server 2000 or earlier, the object must have an owner. Most of the time, the owner is “dbo”, also known as the database owner.

share|improve this answer
    
@RemusRusanu, using the 'schema vs. owner' example was but a means to explain the idea of why an 'owner' is inherent in SQL Server. I appreciate your answer as well! Well stated ... however I do not believe it "therefore hence" forth degrades this example/answer. :) –  Justin Jenkins May 31 '12 at 9:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.