Great stuff. This has been here a while bit if anyone is interested. Similiar code.

Slightly modified to work with SQLOverview. With it I can hit several servers. Now I get the data I am looking for a way to determine any changes. I have some ideas to determine changes or additions to any database/user/roles. Anyone done anything like this with out a trigger?

IF EXISTS

(SELECT *

FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[DBROLES]')

)

DROP TABLE [tempdb].[dbo].[DBROLES]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [tempdb].[dbo].[DBROLES](

[ServerName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_DBROLES_ServerName] DEFAULT (N'.'),

[DBName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_owner] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_accessadmin] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_securityadmin] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_ddladmin] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_datareader] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_datawriter] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_denydatareader] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[db_denydatawriter] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[createdate] [datetime] NOT NULL,

[updatedate] [datetime] NOT NULL,

[Cur_Date] [datetime] NOT NULL CONSTRAINT [DF__DBROLES__Cur_Dat__3A179ED3] DEFAULT (getdate())

) ON [PRIMARY]

GO

INSERT INTO [tempdb].[dbo].[DBROLES]

EXEC sp_MSForEachDB

'SELECT CONVERT(nvarchar(128), SERVERPROPERTY(''Servername'')) AS ServerName,

''?'' as DBName,

UserName,

Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,

Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,

Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,

Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,

Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,

Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,

Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,

Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter,

createdate,

updatedate,

GETDATE()

from (

select b.name as USERName, c.name as RoleName, b.createdate, b.updatedate

from [?].dbo.sysmembers a

join [?].dbo.sysusers b on a.memberuid = b.uid

join [?].dbo.sysusers c

on a.groupuid = c.uid )s

Group by USERName, createdate, updatedate

order by UserName'

SET ANSI_PADDING OFF