How can I query ASE Sybase database to get all users with creation date and assigned roles?

Question

Can someone please help me to get list of all users, created date and assigned roles to users in Sybase database? I tried all ways but no luck.

Thank you.

I tried to find in syslogins and sp_display but I am unable to find user created date.

Answer 1

• First of all do you have the appropriate privs ?
  To query the syslogins table in the master database, you need to have the sso_role or sa_role system role. To query the sysusers and sysroles tables in each individual database, you need to have the sa_role system role or be the owner of the database.

    EXEC sp_displaylogin
  

displays information about your login account, including the roles that are granted to you. If you have the sso_role or sa_role system roles, they will be listed

• Assuming your good on the above :

syslogins table is located in the master database, while the sysusers and sysroles tables are located in each individual database. You can join the syslogins table with the sysusers and sysroles tables to get a list of all users, their creation date, and assigned roles.

SELECT l.name AS 'User Name', l.createdate AS 'Creation Date', r.name AS 'Role Name'
FROM master..syslogins l
JOIN sysusers u ON l.suid = u.suid
JOIN sysroles r ON u.uid = r.lrid
WHERE u.uid > 2

Above is for users in the current database. If you want to retrieve this information for users in a different database, you need to run the query in that database.

In ASE Sybase ( Unlike Oracle or Teradata ) it is not possible to write a single query that retrieves a list of all users, their creation date, and assigned roles across all databases. The sysusers and sysroles tables are located in each individual database and contain information about users and roles specific to that database. To get a list of all users, their creation date, and assigned roles across all databases, you would need to run separate queries in each database and combine the results programmatically using a Stored Proc