SQL for list of available users name or user account in Sybase server

If you don't have permission to run DBA commands you may not be able to view the system tables holding the same information.

We also need more information from you. Which Sybase product? (Sybase has several database products). If you mean Sybase ASE, do you mean you want to know about the logins (server-wide), database users (per database) or application users (could be either of these or something else entirely)?

Our application installed one server and the version of the sybase is 12.5.1. Basically, this database server has multiple database and my question was how many of users having an account in that.
Please let me know if you need more information on this.

The ultimate aim is that I would like to create view or stored procedue which should return with  the results of list of active users account on a particular database.

If you are looking for active users in each database, you can hit the sysprocesses table.  As Joe pointed out though, if the application logs all users in the same way and/or pools connections, you are probably out of luck.  You also have to decide if you want or need the Login ID or the User alias from each database.

Depending on which you need, you can join the sysprocesses table (which has one row for each connection plus some extra background processes) with the syslogins table (to get the login name) and the sysdatabases table (to get the database name).  Something like the code snippet below.  If you need the database user names, it gets more complicated because you have to hit the sysusers tables in each of the databases.

One trick for doing that without resorting to cursors and dynamic SQL is to create a view that incorporates each of the application database sysusers tables.  Something like... (see second listing).  To get the database user name you need only join the view to the first query and you are set.

I am not sure what the privilege issues will be.  As Joe pointed out, you may have to deal with that if a stored procedure compiled in your user context cannot read the system tables.

Regards,
Bill

SELECT .....
  FROM sysprocesses p
  JOIN syslogins l
    ON l.id = p.suid
  JOIN sysdatabases d
    ON d.id = p.dbid
 
 
CREATE VIEW ALL_USERS AS
SELECT dbid('first_app_db') as dbid,
       id as uid
       name
  FROM first_app_db..sysusers
UNION ALL
SELECT dbid('second_app_db') as dbid,
       id as uid
       name
  FROM first_app_db..sysusers
UNION ALL
SELECT dbid('third_app_db') as dbid,
       id as uid
       name
  FROM first_app_db..sysusers

Select allOpen in new window

Thanks