There’s often low hanging fruit once you start looking into servers, or you just need to verify settings are correct. Recently I had to verify that all databases had the correct owner for auditing purposes. There are many scripts out there to do this, but I wanted to make a few exceptions, and I didn’t need a lot of extra fluff for this specific task either. I also had a plan to convert this to a policy afterwards to ensure settings were not changed later.
I used two different scripts, one for 2012 servers that might be utilizing Availability Groups and one for 2008 servers that might be using Mirroring. I wanted to allow exceptions for databases that were mirrors or secondaries, because without failing them over, I could not update the owner. These will be fixed on the next scheduled failover, but until then, I don’t want the exceptions mucking up my results.
WHERE clause excludes my allowed owner while also verifying that I don’t have any databases where the ownership has been broken and listed as
NULL. Server roles for AGs and Mirrors are included in the result set so that when I want everything, I can exclude the
WHERE clause and quickly see what’s what.
SQL 2012 (Availability Groups)
SELECT d.name ,[Owner] = suser_sname(owner_sid) ,s.role_desc FROM sys.databases d ,msdb.sys.dm_hadr_availability_replica_states s WHERE (suser_name(owner_sid) <> 'SA' OR SUSER_NAME(owner_sid) IS NULL) AND s.role_desc = 'Primary'
SQL 2008 (Mirrors)
Same as the above query, but while that query above will error when you try to run it on an older version of SQL than 2012 because of the HADR view, this one works while determining if any of your servers have the wrong database owner. Just exclude the
AND statement to show the mirrors.
SELECT d.name ,[Owner] = suser_sname(owner_sid) ,m.mirroring_role_desc ,d.state_desc FROM sys.databases d LEFT JOIN sys.database_mirroring m ON m.database_id = d.database_id WHERE (suser_name(owner_sid) <> 'SA' OR SUSER_NAME(owner_sid) IS NULL) AND (m.mirroring_role_desc <> 'mirror' OR m.mirroring_role_desc IS NULL)
Afterwards, I used Policy Based Management to create a policy checking for DB ownership. I chose the simple way of creating a Database Facet checking that
@Owner = 'SA'. (Rename your account, but for demonstration purposes this is the most obvious). The policy will check every database, but automatically skip mirrors/secondaries because it cannot read those databases. The script remains useful for checking the databases that the policy cannot read.