Report all users in server databases with dbo permissions

This is from an idea from here.

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
	DROP TABLE #t1

SELECT  CAST('?' AS VARCHAR(128)) AS [Database Name],
        [su1].[name] AS [Database User Name],
        [su2].[name] AS [Database Role]
INTO    [#t1]
FROM    [sys].[database_role_members] [r]
INNER JOIN .[sysusers] [su1]
ON      [su1].[uid] = [r].[member_principal_id]
INNER JOIN .[sysusers] [su2]
ON      [su2].[uid] = [r].[role_principal_id]
WHERE   [su2].[name] IN ('db_owner')
        AND [su1].[name] NOT IN ('dbo')
        AND 1 = 2

INSERT [#t1]
        ([Database Name],
		[Database User Name],
         [Database Role]
        ) 
EXEC sp_MSForEachDB 'SELECT ''?'' AS [Database Name], su1.name AS [Database User Name], su2.name AS [Database Role]
						FROM [?].sys.database_role_members r
							INNER JOIN [?]..sysusers su1 ON su1.[uid] = r.member_principal_id
							INNER JOIN [?]..sysusers su2 ON su2.[uid] = r.role_principal_id
						WHERE su2.name IN(''db_owner'') AND su1.name NOT IN(''dbo'')'
DELETE
FROM [#t1]
WHERE [Database Name] IS NULL

SELECT [Database Name],
       [Database User Name],
       [Database Role]
FROM [#t1]

GO

Comments are closed.

Post Navigation