A Script A Day - Day 5 - Database Owner Permissions

Today's script will list all principals with membership in the db_owner fixed database role.

/*

      -----------------------------------------------------------------

      Database Owner Permissions

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

IF OBJECT_ID('tempdb..#tmp') IS NULL

    CREATE TABLE #tmp

        (

          Principal VARCHAR(250),

          DatabaseName VARCHAR(250)

        );

GO   

EXEC sp_Msforeachdb 'use [?];

INSERT #tmp SELECT  u.name AS Principal, db_name() AS DatabaseName

FROM sys.database_role_members drm

INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id

INNER JOIN sysusers u ON u.uid = drm.member_principal_id

WHERE dp.name = ''db_owner''

AND dp.name <> ''dbo'' AND u.isntuser = 0';

GO

SELECT  Principal,

        DatabaseName

FROM    #tmp

WHERE Principal != 'dbo';

GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

    DROP TABLE #tmp;

GO

Enjoy!


Chris