Report all users in server databases with dbo permissions

\ by

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
Posted in: Coding, Permissions

Report on SQLAgent role members

\ by

Just remove the filter to see everyone.


;WITH    [RoleMembers]([member_principal_id], [role_principal_id])
          AS (
              SELECT    [rm1].[member_principal_id],
                        [rm1].[role_principal_id]
              FROM      [msdb].[sys].[database_role_members] [rm1] (NOLOCK)
              UNION ALL
              SELECT    [d].[member_principal_id],
                        [rm].[role_principal_id]
              FROM      [msdb].[sys].[database_role_members] [rm] (NOLOCK)
              INNER JOIN [RoleMembers] AS [d]
              ON        [rm].[member_principal_id] = [d].[role_principal_id]
             )
    SELECT DISTINCT
            [rp].[name] AS [database_role],
            [mp].[name] AS [database_user],
            [mp].[type]
    FROM    [RoleMembers] [drm]
    JOIN    [msdb].[sys].[database_principals] [rp]
    ON      ([drm].[role_principal_id] = [rp].[principal_id])
    JOIN    [msdb].[sys].[database_principals] [mp]
    ON      ([drm].[member_principal_id] = [mp].[principal_id])
    WHERE   [rp].[name] LIKE 'SQLAgent%'
            AND [mp].[name] NOT LIKE '##%'
    ORDER BY [rp].[name]

This should produce output similar to this:

 or

database_role	database_user	type
SQLAgentOperatorRole	PolicyAdministratorRole	R
SQLAgentReaderRole	PolicyAdministratorRole	R
SQLAgentReaderRole	SQLAgentOperatorRole	R
SQLAgentUserRole	dc_admin	R
SQLAgentUserRole	dc_operator	R
SQLAgentUserRole	MS_DataCollectorInternalUser	S
SQLAgentUserRole	PolicyAdministratorRole	R
SQLAgentUserRole	SQLAgentOperatorRole	R
SQLAgentUserRole	SQLAgentReaderRole	R
Posted in: Coding, Permissions, SQLAgent