Wednesday, August 14, 2013

Database Owner Unknown

I've been changing all of my MS SQL databases to the Simple recovery model, so I don't have to worry about transaction log backups and truncation. I only left really important ones that need point-in-time rollback available on full. While trying to change one of my 'ReportServer' databases to simple, I was not able to right-click on it in SQL Server Management Studio (SSMS) and select properties. I always got the following error:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database '[DBName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Now, I was logged in as the 'sa' account, so "insufficient access" isn't the problem. The problem was that the database in question HAD NO OWNER!!!

I verified this by running the following query against the master database:
sp_helpdb ReportServer

The runs a stored procedure (the 'sp' part) called 'helpdb' which displays info about the database you choose (ReportServer). This query returned a table of info, and the owner field said '~~~UNKNOWN~~~'. Not so good.

The fix was simple - use another stored procedure:
sp_changedbowner 'sa'

The query ran successfully, and I was able to then access the properties of the ReportServer database and change the recovery model.

No comments:

Post a Comment