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?
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.
Since 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:
dbo (the database principal), or some other database principal, and thus the owner is contained with the databaseEXECUTE 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 :)
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
dbois 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.
