Thursday, August 30, 2018

Property Owner is not available for Database

One of my customers just sent me an email, said he could not look at the properties of his database in SSMS.  When he tried to, he received this error:

Curious.  I've actually never seen that before, so I ran sp_helpdb, and it told me the owner was <UNKNOWN>.  Again, very odd... but also very quick to fix.

           USE DatabaseName;
           EXEC sp_changedbowner 'sa';

I him know he could access his database, and then I used this to look into sys.databases to check out the other database owners --

           SELECT suser_sname( owner_sid ),name,state_desc
           FROM sys.databases

Turns out nearly every one of their databases are owned by domain logins.  You've heard all of the warnings about that, right?  If the domain login that owns the database is removed, disabled, or simply cannot be resolved in AD, this will cause problems exactly like this one.  This is also a good reason not to have SQL Server Agent jobs owned by domain logins.  It opens the door for too many problems... that can be easily avoided.