SQL Server User and Group Permissions

set nocount on

DECLARE @SQL VARCHAR(2000)

DECLARE @DBName VARCHAR(100)

CREATE TABLE [##Tbl_sysusers]

(

       [ServerNM] [nvarchar] (25) NULL,

       [DatabaseNM] [nvarchar] (100) NULL,

       [UserNM] [nvarchar] (100) NULL,

       [GroupNM] [nvarchar] (50) NULL,

       [isSQLRole]  [nchar]    (1)   NULL

)

DECLARE DBNameCR CURSOR FOR SELECT name FROM master..sysdatabases

OPEN DBNameCR

FETCH NEXT FROM DBNameCR INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = --'EXEC '+@DBName+'..sp_helpuser'

'SELECT @@ServerName AS ServerNM,'''+@DBName+''' AS DatabaseNM,A.name AS User_NM,B.name AS Database_Role, A.issqlrole as isSQLRole FROM ['+@DBName+']..sysusers A INNER JOIN ['+@DBName+']..sysmembers C ON A.uid = C.memberuid INNER JOIN ['+@DBName+']..sysusers B ON C.groupuid = B.uid

UNION

SELECT @@ServerName AS ServerNM,'''+@DBName+''' AS DatabaseNM,A.name AS User_NM,B.name AS Database_Role, A.issqlrole as isSQLRole FROM ['+@DBName+']..sysusers A INNER JOIN ['+@DBName+']..sysusers B ON A.altuid = B.uid WHERE A.isaliased = 1'

--PRINT @SQL

INSERT INTO [##Tbl_sysusers] Exec (@SQL)

FETCH NEXT FROM DBNameCR INTO @DBName

END

CLOSE DBNameCR

DEALLOCATE DBNameCR

SELECT ServerNM,DatabaseNM,UserNM,GroupNM, isSQLRole FROM [##Tbl_sysusers]

DROP TABLE [##Tbl_sysusers]