Friday, December 2, 2016

DBOWNER

CREATE TABLE ##DBOwners
(
    DBName SYSNAME,
    Username SYSNAME,
    Rolename NVARCHAR(50),
    IsOwner BIT
);

EXEC sp_MSforeachdb
    '
        USE [?];
        INSERT INTO ##DBOwners
            SELECT
                DB_NAME(),
                dp.name,
                dp2.name,
                CASE
                    WHEN dp2.name = ''db_owner'' THEN 1
                    ELSE 0
                END AS ''db_owner''
            FROM
                sys.database_principals AS dp
            LEFT JOIN
                sys.database_role_members AS rm
                    ON
                        rm.member_principal_id = dp.principal_id
            LEFT JOIN
                sys.database_principals AS dp2
                    ON
                        rm.role_principal_id = dp2.principal_id
            WHERE
                dp.type <> ''R''
    '

SELECT * FROM ##DBOwners  where Username <> 'dbo' and rolename ='db_owner' and Username <> 'w4access'

No comments:

Post a Comment