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.
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.
From 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.
It 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.
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