Extract User-Role Mapping

This script can be used with SQL 2000 and 2005. For example if you have 10 users in current database, it will retrieve all Group information along with default database, if the user has a login account else if will return a blank.


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