SQLGeeksPro

Just after returning from Christmas holiday, I got a requirement to send a report having a list of users with db_owner role in all databases in SQL Server Instance. There was an internal audit going on and the auditor wanted that report.
I knew my dear friend “Internet” will help me get that report in lieu of few clicks. I really didn’t want to spend time on writing a whole new script as I was still in holiday mood.

Wait a sec! I think I already had something which I could modify a bit to get the work done. What I had was List all permissions for a user in all or selective databases

But the output is something which I cannot send a report since it shows blank rows for the databases on which the user does not have access to as shown below:

So, I used a temporary table to get the result I wanted. In fact I have added the same in the above article as well upon realization of the problem.

There are two ways you can pull the same report.

• Using Cursor to scan through all the databases.
• Using the undocumented stored procedure sp_MSforeachdb.

Using Cursor to retrieve a List of users with db_owner role in all databases:

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

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

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #UserPermission

DROP TABLE #UserPermission

Sample output:

Using sp_MSforeachdb to retrieve the same information:

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_MSforeachdb '

use [?]

BEGIN

  SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
  pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
  FROM sys.database_role_members roles
  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
  where pp.name=''db_owner'' and p.name<>''dbo'' 

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission

Sample output is same as above:

Query to Retrieve List of all users having db_owner role in Selective databases:

DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
ServerName SYSNAME,
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
TypeOfRole VARCHAR(50)
)

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('master','ReportServerSSRS','ReportServerSSRSTempDB') -- change the database names as applicable
AND state_desc='online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ 'SELECT
ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
pp.name as PermissionLevel, pp.type_desc as TypeOfRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #UserPermission

DROP TABLE #UserPermission

Sample output of selective databases:

Using sp_MSforeachdb to list users with db_owner role only in user databases:

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_MSforeachdb '

use [?]

IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN

  SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
  pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
  FROM sys.database_role_members roles
  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
  where pp.name=''db_owner'' and p.name<>''dbo''   

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission

Sample result set:

Hope this helps. If you have any suggestion please feel free to put in comments section.

You may also find the following scripts useful for your day to day life:
Script to find the SQL Agent Job Name in Execution
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000