• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

script all db role for all db

Hello,

I search a script for having all db role for all db.

Thanks

bibi
0
bibi92
Asked:
bibi92
1 Solution
 
JestersGrindCommented:
The attached script will list the users and the database roles that they are in for each database.

Greg


EXECUTE sp_msforeachdb 'select  ''[?]'' as DatabaseName,
             u.name
            ,case when (r.principal_id is null) then ''public'' else r.name end AS [Role]
            ,l.default_database_name
            ,u.default_schema_name
            ,u.principal_id
    from [?].sys.database_principals u
        left join ([?].sys.database_role_members m join [?].sys.database_principals r on m.role_principal_id = r.principal_id) 
            on m.member_principal_id = u.principal_id
        left join [?].sys.server_principals l on u.sid = l.sid
        where u.type <> ''R''
        ORDER BY [Name], [Role]'

Open in new window

0
 
bibi92Author Commented:
Thanks bibi
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now