Change Database Owner

This is post is going to be very simple but it is something I come across ever so often and find myself having to look up the syntax most of the time.  I guess it is one of those things that if you don’t use it often enough you don’t commit it to memory.

So here is my story, I routinely come across database servers that when you go to look at the properties of the database you get that dreadful message about the “Property Owner is not available for Database ‘[db_name]’.  This property may not exist for this object, or may not be retrievable due to insufficient access rights.”  It is a very simple fix if you can remember the stored procedure to execute.  All you have to do is assign the proper user as DBO.

The bigger question is how did this issue occur in the first place.  For those of you novice DBA’s you may or may not be aware that when you restore a database you become the owner of the database unless you change it.

So how to correct this.  Simple.  EXEC sp_changedbowner ‘SA’  You can substitute another account in place of SA if that is your companies practice.

EDIT >> A friend by the name of Nicholas Cain pointed out that ALTER AUTHORIZATION can accomplish the same task.   I believe this is new to SQL 2005 and as I still support SQL 2000 🙁 sp_changedbowner is still needed.  However going forward I do think I will start using the following syntax at least on all 2005 and above systems.

ALTER AUTHORIZATION ON DATABASE :: DB_NAME TO SA