How to Track and Monitor All Database Owners in Your SQL Server

Having a check on database owners is very important. You might hit a service broker problem and then realize that the root cause was a windows login of an ex-employee and it was set as the database owner. If you proactively track database owners, you will never meet such issues in your work environment.

Not DBO

In this article, we are dealing with database owner and not do or db_owner. DBO is a user, it’s a database concept and db_owner is a fixed database role.

Log it

Monitor Database OwnersFrom your central server, create a new query window and run this script. It is a prerequisite that you have already created Linked Server for all SQL Servers from which we would be collecting database owner info. Through this script, we are creating a single column table to save all SQL Server names. We will create another table which would act as our log table. This table would have 3 columns. The first is the server name, second is the database name and the last column is the owner name. Using a cursor, we will be creating dynamic TSQL statements and triggering it against the linked server. The collected info is then saved into the Log table. The script mentioned in this article uses the table sys.databases to retrieve database owner information.

DECLARE @Tserver TABLE 
  ( 
     cserver VARCHAR(200) 
  ) 

INSERT INTO @Tserver 
VALUES      ('Server1') 

CREATE TABLE tlog 
  ( 
     cserver VARCHAR(200), 
     cdb     VARCHAR(200), 
     cowner  VARCHAR(2000) 
  ) 

DECLARE c1 CURSOR FOR 
  SELECT * 
  FROM   @Tserver 
DECLARE @cmd VARCHAR(2000) 

OPEN c1 

FETCH next FROM c1 INTO @cmd 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      DECLARE @server NVARCHAR(2000) 

      SET @server = 'insert into Tlog select ''' + @cmd 
                    + ''', name, suser_sname(owner_sid) from [' 
                    + @cmd + '].master.sys.databases' 

      PRINT @server 

      EXEC (@server) 

      FETCH next FROM c1 INTO @cmd 
  END 

CLOSE c1 

DEALLOCATE c1 

SELECT * 
FROM   tlog

Scan for non-compliance

You can now filter this table for non-compliance i.e., look for database owners who have already left the organization. Using ALTER AUTHORIZATION ON DATABASE you can update the owner information for a database. You can use SA or a domain account and set it as owner of the database. This will help us to monitor database owners.  If you have deleted the domain account that owns a database, then you are making the database as an orphaned database. Now you would not be able to view database properties using a SQL Server management Studio.

Rare requirement

Track And Monitor SQL DatabaseIt is very rare that an application would require all databases created should have a specific account as database owner. Other than such databases you are free to modify the database owner. It is not just the deleted database owner information or a null database owner property that would restrict you from viewing database properties. If you are having a corrupted MDF file on your SQL instance, it would also block you from listing database properties.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook and excel recovery software products. For more information visit www.datanumen.com

2 responses to “How to Track and Monitor All Database Owners in Your SQL Server”

  1. Hi Li,
    Seems this is for only a specific SQL server instance. How would I gather the db_owner from all SQL server’s where linked server connection established.

    Please share if do you have any scripts with you.