###001 SQL Script to return user accounts and the roles they are assigned to.....

-- Process
-- Create Temp Table for Users
-- Create Temp Table for Roles
-- Populate Users
-- Populate Roles
-- Iterate though each user and update their roles into a single column
-- Return the users and their roles
Create Table #Temp_Users
(
DatabaseUserName varchar(128),
SYSLoginname varchar(128),
CreateDate datetime,
LastModifiedDate datetime,
LoginType varchar(50),
Roles varchar(1024)
)

Create Table #Temp_Roles
(
Name varchar(128),
Role varchar(128)
)

insert into #Temp_Users
select a.name,
b.Loginname,
[Create Date] = a.CreateDate,
[Last Modified Date] = a.UpdateDate,
LoginType = case
when a.IsNTName = 1 then 'Windows Account'
when a.IsNTGroup = 1 then 'Windows Group'
when a.isSqlUser = 1 then 'SQL Server User'
when a.isAliased =1 then 'Aliased'
when a.isSQLRole = 1 then 'SQL Role'
when a.isAppRole = 1 then 'Application Role'
else 'Unknown'
end,
Roles = ''
from sysusers a
left outer join master..syslogins b on a.sid=b.sid
where a.SID is not null
order by a.Name

insert into #Temp_Roles
select MemberName = u.name, DbRole = g.name
from sysusers u, sysusers g, sysmembers m
where g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid
order by 1, 2



Declare @Name varchar(128)
Declare @Roles varchar(1024)
Declare @Role varchar(128)

DECLARE UserCursor CURSOR for
SELECT DatabaseUserName from #Temp_Users
OPEN UserCursor
FETCH NEXT FROM UserCursor into @Name
WHILE @@FETCH_STATUS = 0
BEGIN
set @Roles = ''
print @Name
DECLARE RoleCursor CURSOR for
SELECT Role from #Temp_Roles where Name = @Name

OPEN RoleCursor
FETCH NEXT FROM RoleCursor into @Role
WHILE @@FETCH_STATUS = 0
BEGIN
if (@Roles > '')
set @Roles = @Roles + ', '+@Role
else
set @Roles = @Role
FETCH NEXT FROM RoleCursor into @Role
end
Close RoleCursor
DEALLOCATE RoleCursor
Update #Temp_Users set Roles = @Roles where DatabaseUserName = @Name
FETCH NEXT FROM UserCursor into @Name
END
CLOSE UserCursor
DEALLOCATE UserCursor

update #Temp_Users
set Roles='public'
where Roles=''

select * from #Temp_Users

drop table #Temp_Users
drop table #Temp_Roles