Property Owner is Not Available for Database…

Property Owner is Not Available for Database

One of the Most annoying error Messages(which really irritates a novice DBA) when you try to look at your Database Properties/Options from SQL Server Management Studio(SSMS). You’ll get something similar to the below screenshot when you right click on the database and choose properties.

Okay…So Let’s run our legacy sp_helpdb and see the result set.

Well, it says the owner of ‘Adventureworks’ database is “UNKNOWN”…Umm Interesting!! So what can we do to fix this issue?

Resolution:  Assigning an owner for this database from T-SQL. Typically i prefer to go with ‘sa’ as Database Owner for all the databases. We can do this in either way mentioned below

Legacy(SQL 2000) way:  

Use Adventureworks
GO
sp_changedbowner ‘sa’

New way starting SQL Server 2005:

ALTER AUTHORIZATION ON DATABASE::Adventureworks TO sa.

So what is the root cause for this strange behavior of  database from SSMS? What happened exactly?? Well, consider a scenario where your coworker has created a database and he/she(Windows Account) is the current owner of the database(Just let’s assume). For some reason he/she left the organization and his/her Windows Account will be removed from the Active Directory by AD Administrator(and as a DBA you’ll drop his/her login from SQL Server). In other words his/her windows login is no longer a valid account.

Illustration:

I’ve created a windows user account called “baddba” and I’ve added him as a login in my SQL Server and made him the Database Owner for Adventureworks database as shown below.

Let’s me simulate a scenario where he/she leaves our company and our AD Admin removes his/her account and another DBA drops the login from SQL Server.(Actually i did both the steps behind the scenes).

Now let’s try to select database properties from SSMS….

Grrrrrrrrr…..same Error!!!

Conclusion: Try to avoid having individual Login as a database Owner. Definitely a very bad practice as justified in this post! Instead, make it ‘sa’ on all the databases. You can make use of Powershell to achieve this if you are really supporting tons and tons of databases and have no patience to deal with individual database(s) as i do:)

 

Advertisements

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