Script to Identify what fixed Database Roles a Login belongs to in all Databases on an Instance

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 23, 2012 at 5:26 am

  #251948

  I'm looking for a script that identifies what fixed database roles a Login belongs to in all Databases in an instance.

  Any help would be greatly appreciated.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Ant-Green

  SSC Guru

  Points: 113565

  More actions

  February 23, 2012 at 7:06 am

  #1450830

  might want to wrap this in sp_msforeachdb and strip out some of the stuff you dont need

  select * from sys.database_role_members drm

  join sys.database_principals dp

  ON drm.role_principal_id = dp.principal_id

  join

  sys.database_principals dp2

  on

  drm.member_principal_id = dp2.principal_id

  where dp.type = 'R'

  and dp2.type_desc like '%user%'