Extract User group & its database information

Hi,

Following script can be used with SQL 2000, 2005 & 2008. 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. This also eliminates system users (DBO, SYS, INFORMATION_SCHEMA and GUEST). Although we can use sp_helpuser procedure but it will return all the user's information including system users. A loop is used to get information per user rather than all users.

Begin
--Create a temp table that will hold the main result. This script will check your version, if SQL 2005 or 2000 or 2008,
--because sp_helpuser return 6 values in SQL 2000 and 7 values in SQL 2005 / 2008.
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)
Declare @maxval int
Declare @minval int

--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','INFORMATION_SCHEMA', 'SYS')" from ---the following statement.

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

Select @maxval = max(ID), @minval = min(ID) from #TmpUser

--Execute the below query to get current version of SQL SERVER

set @ver = convert(varchar(100),SERVERPROPERTY('productversion'))

if (left(@ver,2) = '9.' or left(@ver,2) = '10') --If SQL 2005 or 2008 then
begin
--Run a loop for all users
While (@minval <= @maxval)
BEGIN
Select @name = name from #TmpUser where ID = @minval
--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
Set @minval = @minval+1
END

END
else --If SQL SERVER 2000 or other
begin
--Run cursor for all the user names
While (@minval <= @maxval)
BEGIN
Select @name = name from #TmpUser where ID = @minval
--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
Set @minval = @minval+1
END
end

--Now show data & drop temp tables.
select * from #tmpUserPerm order by 1
drop table #Tmpuser
drop table #tmpUserPerm

end

Let me know if you have any comments.

Regards,

Sudhir