SQLGeeksPro

Hello, Today we I am going to provide you with a query to List SQL Database Role Owner Across All Databases. In the article Query To List SQL Database Role Owner we have seen how to List The Database Role Owner for a single Database.

This query will be nice to have in the repository in case you want a audit report for all databases on a SQL Instance.

DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)
CREATE TABLE #databaseRoleOwner
(
        [DatabaseName] sysname,
	[RoleName] varchar(100),
	[RoleOwner] varchar(100)
);

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE  state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN
SELECT @statement = 'use ['+@dbname +'];'+ 'SELECT dbname=db_name(db_id()),
    name AS RoleName,
    USER_NAME(owning_principal_id) AS RoleOwner
FROM
    sys.database_principals
WHERE
    type = ''R'' and name NOT IN (''public'', ''db_owner'', ''db_accessadmin'',''db_securityadmin'', ''db_ddladmin'',''db_backupoperator'',''db_datareader'',
	''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')'

INSERT INTO #databaseRoleOwner ([DatabaseName],[RoleName],[RoleOwner])
EXEC  sp_executesql @statement
 
FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #databaseRoleOwner
GO
 -- DROP TABLE #databaseRoleOwner

Example Result set:

 

Let me know your thoughts by leaving comments below. The following scripts are also useful for day to day DBA life.

Query To List All Orphaned Logins In SQL Server

List All Database Users With Permissions

Script To List SQL Agent Jobs and Schedules

SQL Server Select Data Between Two Dates