Code:
SQL Query 3 Creates Table
select distinct sl.suid as slsuid ,sl.name as slname , CONVERT(VARCHAR(512), ssr.name) as ssrname
into tempdb.dbo.Sybase_USER_ROLES
from master.dbo.syslogins sl
join master.dbo.sysloginroles slr
on slr.suid = sl.suid
join master.dbo.syssrvroles ssr
on ssr.srid = slr.srid
Order by sl.suid asc
SET @LastSuidCode = 0,
@LastRoleDesc = ''
UPDATE tempdb.dbo.Sybase_USER_ROLES
SET ssrname = CASE WHEN slsuid = @LastSuidCode
THEN @LastRoleDesc + ssrname + ','
ELSE ssrname + ',' END,
@LastRoleDesc = CASE WHEN slsuid = @LastSuidCode
THEN @LastRoleDesc + ssrname + ','
ELSE ssrname + ',' END,
@LastSuidCode = slsuid
use tempdb
go
Select a.ServeName,a.SybLoginName, a.EMPID,a.PasswordExpInt,a.PwdLastChgDate,a.PasswordExpDate,a.LoginLocked,a.LoginExpired,a.NumDaysToExp,b.ssrname
from tempdb.dbo.SYBASE_EXPIRED_LOGINS a, tempdb.dbo.Sybase_SECURITY_USERS_AUD_ROLE b
where a.SybSuid *= b.slsuid
order by a.SybSuid
QUERY 3 Output
ServeName,SybLoginName,EMPID,PasswordExpInt,PwdLastChgDate,PasswordExpDate,LoginLocked,LoginExpired,NumDaysToExp,ssrname
'OLTP_DEV','sa','N',0,2010-03-11 18:44:51.713,2010-03-11 18:44:51.713,'YES','No',0,'oper_role,replication_role,sa_role,sso_role,sybase_ts_role,'
'OLTP_DEV','probe','N',0,2009-12-05 14:32:47.686,2009-12-05 14:32:47.686,'No','No',0,
'OLTP_DEV','TPCS','N',0,2005-12-20 10:10:00.0,2005-12-20 10:10:00.0,'No','No',0,
'OLTP_DEV','alvarte','N',90,2013-04-18 16:05:53.33,2013-07-17 16:05:53.33,'No','No',-447,
'OLTP_DEV','brownra','N',90,2003-04-28 08:23:00.0,2003-07-27 08:23:00.0,'No','No',-4090,
'OLTP_DEV','carutji','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','cron_user','N',90,2004-06-18 09:25:00.0,2004-09-16 09:25:00.0,'YES','No',-3673,
'OLTP_DEV','davismi','N',90,2005-01-21 10:30:00.0,2005-04-21 10:30:00.0,'No','No',-3456,
'OLTP_DEV','dialja','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'YES','No',-5124,
'OLTP_DEV','dischro','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','gastoji','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','goelpoo','N',90,2014-08-12 14:44:42.28,2014-11-10 14:44:42.28,'No','No',34,
'OLTP_DEV','powersite','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','prasaap','N',90,2014-03-06 14:41:22.913,2014-06-04 14:41:22.913,'YES','No',-125,'mon_role,navigator_role,oper_role,sa_role,sso_role,sybase_ts_role,'
'OLTP_DEV','sarabsa','N',90,2012-09-18 11:54:56.846,2012-12-17 11:54:56.846,'YES','No',-659,
'OLTP_DEV','sburdett','N',90,2014-03-26 09:54:35.366,2014-06-24 09:54:35.366,'No','No',-105,'dtm_tm_role,ha_role,mon_role,navigator_role,oper_role,replication_role,sa_role,sso_role,sybase_ts_role,'
'OLTP_DEV','seligri','N',90,2003-08-04 10:02:00.0,2003-11-02 10:02:00.0,'No','No',-3992,
'OLTP_DEV','hicksan1','N',90,2000-10-05 09:57:00.0,2001-01-03 09:57:00.0,'No','No',-5025,'dtm_tm_role,ha_role,mon_role,navigator_role,replication_role,'
'OLTP_DEV','mcdonka','N',90,2004-07-27 08:57:00.0,2004-10-25 08:57:00.0,'No','No',-3634,'mon_role,'
'OLTP_DEV','bohnech','N',90,2012-04-30 12:11:13.623,2012-07-29 12:11:13.623,'YES','No',-800,
'OLTP_DEV','sybase','N',0,2000-11-08 14:25:00.0,2000-11-08 14:25:00.0,'No','YES',0,
'OLTP_DEV','EAS1','N',90,2000-12-14 14:16:00.0,2001-03-14 14:16:00.0,'No','No',-4955,