SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Find and fix SQL Server databases with empty owner property using dbatools PowerShell module

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

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

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

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

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

Test-DbaDatabaseOwner_default
If we specify the

–Detailed
  parameter we can see all databases, regardless of owner.

Test-DbaDatabaseOwner -SqlServer sql2008 -Detailed

Test-DbaDatabaseOwner_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

Test-DbaDatabaseOwner_default_emptyOwner

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

Test-DbaDatabaseOwner_default_filterby_emptyOwner

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

Test-DbaDatabaseOwner_default_mutipleServers

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.
Set-DbaDatabaseOwner_MutipleServers
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.
Test-DbaDatabaseOwner_detailed_afterFix
Also, from SSMS we can see that the ~~UNKNOWN~~ is gone.

ssms_helpdb_mutipleservers

Owner is now ‘sa’

owner_Not_empty_ssms2016

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

 

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.

Cláudio Silva

I am a SQL Server DBA working from SQL2000 to SQL2016. I love using PowerShell to make my life ridiculously easier! I contribute to dbatools PowerShell open source module.

Comments

Leave a comment on the original post [redglue.org, opens in a new window]

Loading comments...