Extract User-Role Mapping

Begin
--Create a temp table that will hold the main result. This script will check your version, if SQL 2005 or 2000,
--because  sp_helpuser return 6 values in SQL 2000 and 7 values in SQL 2005.
    Create table #tmpUserPerm
    (UserName varchar(100),
    GroupName varchar(100),
    LoginName varchar(100),
    DefDBName varchar(100),
    UserId int,
    SID varbinary(100),
    DefSchemaName varchar(100) null)

    Declare @name varchar(100)
    Declare @ver varchar(100)

    --Create a temp table that will store all users except DBO and GUEST. If you want all users then 
    --you can remove "and name not in ('DBO', 'GUEST')" from the following statement.


    select uid, name into #TmpUser from sysusers 
    where issqluser = 1 and hasdbaccess <> 0 and name not in ('DBO', 'GUEST')

    --Execute the below query to get current version of SQL SERVER
    set @ver = convert(varchar(100),SERVERPROPERTY('productversion'))

    if (@ver = '9.00.3054.00') --If SQL 2005 then 
    begin
--Run a cursor for all users
       declare cur Cursor for Select name from #Tmpuser
       open cur
       fetch next from cur into @name
       while @@fetch_Status = 0
       BEGIN
   --Get data from sp_helpuser for current value of user (@NAME)
           insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName, DefSchemaName, UserId, SID)
           Exec sp_helpuser @name
           fetch next from cur into @name
       END
       close cur
       deallocate cur
       drop table #Tmpuser
       select * from #tmpUserPerm order by 1
       drop table #tmpUserPerm
    END
    else --If SQL SERVER 2000 or other 
    begin
--Run cursor for all the user  names
       declare cur1 Cursor for Select name from #Tmpuser
       open cur1
       fetch next from cur1 into @name
       while @@fetch_Status = 0
       BEGIN
--Get data from sp_helpuser for current value of user (@NAME)
          insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName, UserId, SID)
          Exec sp_helpuser @name
          fetch next from cur1 into @name
       END
       close cur1
       deallocate cur1
       drop table #Tmpuser
       select username, groupname, loginname, defdbname from #tmpUserPerm order by 1
       drop table #tmpUserPerm
    end
end