Get a list of users of a database.

For example, I have a database MyDatabase with 6 users A, B, C, D, E, F. This database is in a server that has 3 System Admins adminA, adminB, adminC. How can I get a table (return by select statement or SP) that has 2 columns Type, Name with rows are information about 6 users and 3 system admins. This is the table I want to have:
------------------------------------------------
Type                                     Name
User                                      A
User                                      B
User                                      C
User                                      D
User                                      E
User                                      F
Sysadmin                               adminA
Sysadmin                               adminB
Sysadmin                               adminC
-----------------------------------------------
I want to do that when I am a normal user (user A, for example) or a system admin (adminA, for example).
Thanks.
LVL 3
maidinhtaiAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
    - 6 users of MyDatabase.
     - 3 system admins of MyServer.

ok, i guess you are speaking about LOGINS (on server level) and USERS (on database level)
how do you identity the "users" and the "sysadmin" ?

in sql server 2000, the logins are here:
select * from master.dbo.syslogins
in sql server 2005:
select * from master.sys.logins

the users are found in SQL 2000 (per current database):
select * from dbo.sysusers
in SQL 2005:
select * from sys.users


0
 
maidinhtaiAuthor Commented:
Ah, I forget. I want to do that with SQL SERVER 2000. If your solution can work with both SQL SERVER 2000 and SQL SERVER 2005, so it will be very wonderful.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you do have the table or what? not very clear what you have and what you want!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
maidinhtaiAuthor Commented:
What I have, for example:
     - A Server MyServer.
     - A Database MyDatabase.
     - 6 users of MyDatabase.
     - 3 system admins of MyServer.
What I want:
     - get the above table that I try to describe. This table is return by a select statement or a stored procedure.

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can try
exec sp_helpUsers       -- to list all the users in the current database
exec sp_helpLogins     -- to list all the users in the system
0
 
maidinhtaiAuthor Commented:
angelIII,
Here is what I want. Is it optimized?

select Type = 'sysadmin', MemberName = loginname from master.dbo.syslogins where sysadmin = 1 and IsNTName = 0
union
select Type ='User', MemberName = name from dbo.sysusers where IsSQLUser = 1 and HasdbAccess = 1

---------------------------------------
aneeshattingal, I want to have the query return the above table. So that sp_helpUsers and sp_helplogins list all users and logins but they do not return a table. Anyway, thank you.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that looks fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.