A few days ago I came across with a error that says “Property Owner is not available for database [XXXXXXX].”. The following image shows the error message.

SSMS 2008 error opening database properties

I have seen this message before so I know that the problem is that the database does not have a defined owner.

We can confirm this by running the “sp_helpdb [<dbname>]” command on SSMS which will return:

Verify database owner

Why it happens?

When the login defined as owner is a Windows Login but that login has been dropped from AD or the local computer, SQL Server assumes it as ~~UNKNOWN~~.

If we right-click on the database in Object Explorer and try to open properties window for database we will get the error message shown before.

Newest SSMS – Different behaviour

The error message pop up when using SSMS 2008 but when I was trying to reproduce this problem I was using SQL Server Management Studio 2016 and the error message did not appear. Instead, the window was able to be opened and we can see on the ‘Files’ tab the ‘Owner:’ property has empty textbox.

SSSM 2016 shows empty value for ‘Owner:’ textbox

In matter of fact we can even change any property on this or other pages and save the changes without any error.

How can we fix it?

We need to define a new owner to the database.

As we can see on newest SSMS versions we are able to open the properties window which will allow us to set a new database owner. But, if you are using an older SSMS that does not let you open the properties window we need to run the following command:

exec sp_changedbowner '<DatabaseName>'

Or since SQL Server 2005 we have a new syntax

ALTER AUTHORIZATION ON DATABASE::<DatabaseName> TO <NewOwner>

After running this command we can see that ~~UNKNOWN~~ give place to the new defined login.

View new owner

How can I validate if I have other databases with the same problem? Well, I will pick dbatools for the job.

dbatools is an open source PowerShell module on GitHub that have more than 100 commands to make our life easier.

If you are running PowerShell 5 or higher you can run the following command to install it:

Install-Module dbatools

If you still running a lower version (PowerShell 3 or 4) you can install using the followin command:

Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in)

Verify database owners

Test-DbaDatabaseOwner is the command that we can use to verify who are the database owners.

You can test for a different account to be the owner by supplying the –TargetLogin parameter. By default this command tests if ‘sa’ account is the owner of the database.

Running the following command will return all databases where the current owner is not the ‘sa’ account:

Test-DbaDatabaseOwner -SqlServer sql2008

If we specify the

–Detailed

  parameter we can see all databases, regardless of owner.

Test-DbaDatabaseOwner -SqlServer sql2008 -Detailed

Next, we will reproduce our problem by deleting the ‘BASE\DBOwner’ user from AD. As a result the owner will become empty.

Test-DbaDatabaseOwner -SqlServer sql2008

Database DBWithoutOwner as no current owner

Now that we know that ‘CurrentOwner’ column will have an empty string whenever owner is not defined we can take advantage of that and filter the output to only return records where CurrentOwner column is empty.

Test-DbaDatabaseOwner -SqlServer sql2008 |
Where-Object {$_.CurrentOwner -eq ""} | Format-Table

Lets run this validation in a collection of instances and see what we get:

$serverList = @("sql2008", "sql2012", "sql2016")
$databasesWithoutOwner = Test-DbaDatabaseOwner -SqlServer $serverList | Where-Object {$_.CurrentOwner -eq ""}
$databasesWithoutOwner | Format-Table

As you can see we found a database named DBWithoutOwner on sql2008 and sql2012 instances that does not have an owner defined. The sql2016 instance does not show any results because every database has a CurrentOwner.

Set database owner

With our collection of databases, inside our

$databasesWithoutOwner

  variable, we can iterate it and run the Set-DbaDatabaseOwner command to set a new owner to this collection of servers/databases.

Note: By not including the

–TargetLogin

  parameter, the default login that will be used as owner is ‘sa’ account. If you want to set a different owner than ‘sa’, you can using this parameter.

Finally, we can rerun our script against our list of servers (

$serverList

 ) and verify that our databases on each server now have ‘sa’ account as database owner.

Also, from SSMS we can see that the ~~UNKNOWN~~ is gone.

Owner is now ‘sa’

And now, we can open our property windows using SSMS 2008 without any error message

Summary

With this example we could see that SSMS 2016 improved the way it handles older errors. Although this can hide some misconfigurations like this one.

We could also see, by using dbatools PowerShell community module how we can find all occurrences and fix our databases that do not have an owner defined.

You can find more handy commands on this PowerShell module on GitHub and dbatools.io site.

If you have any questions please drop a comment.