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

--#### LOGINS,USERS,ROLES (LUR) SCRIPT ####
--#### SCRIPT TESTED IN SQL SERVER 2000,2005,2008 AND 2008R2####

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

-- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES

CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]( 
	[DBNAME] [SYSNAME] ,
	[USERNAME] [SYSNAME] ,
	[DB_OWNER] [VARCHAR](3) ,
	[DB_ACCESSADMIN] [VARCHAR](3) ,
	[DB_SECURITYADMIN] [VARCHAR](3) ,
	[DB_DDLADMIN] [VARCHAR](3) ,
	[DB_DATAREADER] [VARCHAR](3) ,
	[DB_DATAWRITER] [VARCHAR](3) ,
	[DB_DENYDATAREADER] [VARCHAR](3) ,
	[DB_DENYDATAWRITER] [VARCHAR](3) ,
	[DT_CREATE] [DATETIME] NOT NULL,
	[DT_UPDATE] [DATETIME] NOT NULL,
	[DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE())
) ON [PRIMARY]

GO

INSERT INTO    [TEMPDB].[DBO].[DB_ROLES] 
EXEC SP_MSFOREACHDB 
'	SELECT  
    ''?'' 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'
         
-- RETRIVE LOGINS, USERS AND ROLES TOGETHER

SELECT  SERVERPROPERTY('SERVERNAME') AS [SERVERNAME], 
B.NAME AS [LOGINNAME],
CASE B.SYSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN,
CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN,
CASE B.SETUPADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN,
CASE B.PROCESSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN,
CASE B.DISKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN,
CASE B.DBCREATOR  WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR,
CASE B.BULKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN,
B.DBNAME AS [DEFAULT_DBNAME],
A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME 

--WHERE B.ISNTUSER=1  --INCLUDE TO EXCLUDE THE SQL LOGINS

SELECT * FROM #LOGINS ORDER BY [LOGINNAME]

DROP TABLE  [TEMPDB].[DBO].[DB_ROLES]
DROP TABLE  #LOGINS

--------------SCRIPT END-----------------------