Get a list of users of a database.

Posted on 2006-04-26
Last Modified: 2012-05-05
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).
Question by:maidinhtai
    LVL 3

    Author Comment

    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.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    so you do have the table or what? not very clear what you have and what you want!
    LVL 3

    Author Comment

    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.

    LVL 142

    Accepted Solution

        - 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

    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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
    LVL 3

    Author Comment

    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.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    that looks fine.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now