It's crude, but it serves the purpose for which it's intended. It shows users, databases and roles for each user, and permissions granted directly to each user.
It DOES NOT show the specific permissions granted to the roles that a user belongs to, nor does it show permissions granted to public or guest.
declare @name sysname, @sid nvarchar(4000), @qry varchar(8000)
declare crs cursor local for
select name, master.dbo.fn_varbintohexstr(sid) from master.dbo.syslogins --where name = 'fred'
order by name
open crs
fetch next from crs into @name, @sid
while @@fetch_status = 0
begin
print ''; print ''
print replicate('=',230)
print '== User: '+@name
print replicate('=',230)
print ''
exec sp_helplogins @name
set @qry = 'if exists(select 1 from [?].dbo.sysusers where master.dbo.fn_varbintohexstr(sid) = '''+@sid+''') '+
'begin '+
'declare @n sysname, @u int, @m varchar(100); '+
'select @u = uid, @n = name from [?].dbo.sysusers where master.dbo.fn_varbintohexstr(sid) = '''+@sid+'''; '+
'if exists(select 1 from [?].dbo.sysprotects where uid = @u) '+
'begin '+
'set @m = ''** Database: [?] **''; print replicate(''*'',len(@m)); print @m; print replicate(''*'',len(@m)); '+
'exec [?].dbo.sp_helprotect @username=@n; '+
'end '+
'end'
exec sp_msforeachdb @qry
fetch next from crs into @name, @sid
end
close crs
deallocate crs