Help: SQL Server

No one likes errors in the world. As a part of my work with Microsoft SQL Support Services, I get to see various errors, find cause and provide solution to get rid of them. Today someone reported below error to me.

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

This error message was caused because SQL Server was not able to find context information for the security logon we were attempting to impersonate. In this case it was “dbo”. Going with common sense, I went ahead to check the owner of the database, which is generally visible in database > right click > properties. As soon as I attempted it, I was welcomed with next error message.

TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
——————————
Property Owner is not available for Database ‘[SQLServerHelp]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1746.0+((KJ_RTM_QFE-CU).101005-1216+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476
——————————
BUTTONS:
OK
—————————— 
 

Okay. This is an interesting message. Notice the exception event source is “PropertyCannotBeRetrievedExceptionText” and exception event id “Owner”. The message means that on SMO, the value of “owner” property is not available. This means something is not right with owner of the database?

Next step was to find out who the current owner (without management studio). There are multiple ways to do it.

• sp_helpdb

• sp_helpdb ‘DatabaseName’

• T-SQL which is used by sp_helpdb

select name, isnull(suser_sname(sid),'~~UNKNOWN~~'), 
convert(nvarchar(11), crdate),
dbid, cmptlevel from master.dbo.sysdatabases

As we can see above, “~~UNKNOWN~~” is not something which is not expected and that’s the reason SSMS was showing exception to get owner property.

How to fix it?

Of course, change the owner of the database so that value of SID column holds valid SID. Since we are not able to use UI, we have to use sp_changedbowner (change db owner with no spaces). This has be to run in the context of the database which has problem, as shown below. I have given sa as an example for my database.

Root Cause/Repro of the issue

If a database is owned by a login, we would not be able to drop the login. The possible situation which I can think of are:

• Database owned by windows login which was part of any Windows Group. And now, windows account was deleted from active directory. 
• Database was restored and domain controller was not available to resolve the SID to a validate login.

In my repro, I was able to get into same situation by step 1.

Important: I was NOT able to reproduce the “property owner” error in SQL Server 2014 Management Studio. It doesn’t throw exception of owner property and it can be blank in the UI.

This also means that owner can be changed via management studio by going to Properties  > Files tab.

Hope you have learned something new.