display permissions with database name

• ColeTrain

  SSC Veteran

  Points: 240

  More actions

  November 7, 2009 at 6:13 am

  #211938

  Hello,

  I am running the following:

  sp_msforeachdb @command1= "PRINT '?' EXEC ?.dbo.sp_helprolemember db_owner"

  this is providing me the correct results but I am unable to get it to display the database name with the result set?

  I would like to have something showing the Database name above then the permissions below each name for each result.

  DatabaseName

  DbRole MemberNameMemberSID

  db_owner dbo 0x01

  My issue is I need to generate a report for Auditing that shows every DB with each user that has DBO rights to that DB. If I can get that in a single report that would be even better.

  Thanks,

• ColeTrain

  SSC Veteran

  Points: 240

  More actions

  November 7, 2009 at 10:02 am

  #1076360

  I"m able to get the results with:

  sp_msforeachdb @command1= "USE ? select '?' EXEC ?.dbo.sp_helprolemember db_owner"

• lokhande.deepali

  SSC Eights!

  Points: 833

  More actions

  November 9, 2009 at 2:23 am

  #1076540

  Try Below Query..

  sp_msforeachdb @command1= "select '?' as Databasename EXEC ?.dbo.sp_helprolemember db_owner"

  Rd,

  Deepali