Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Troubleshooting: Cannot execute as the database principal and Property Owner is not available for Database

Posted by blakhani on March 4, 2014


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.

image

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

image

  • sp_helpdb ‘DatabaseName’

image

  • 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

 

image

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.

image

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.

image

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

image

Hope you have learned something new.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • 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

     
    %d bloggers like this: