[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 987
  • Last Modified:

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.
0
maidinhtai
Asked:
maidinhtai
  • 3
  • 3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now