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).
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

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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you do have the table or what? not very clear what you have and what you want!
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.

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
maidinhtaiAuthor Commented:
Here is what I want. Is it optimized?

select Type = 'sysadmin', MemberName = loginname from master.dbo.syslogins where sysadmin = 1 and IsNTName = 0
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that looks fine.
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.