SQL QUERY 1: Ceates - SYBASE_EXPIRED_LOGINS Table
use master
go
set nocount on
declare @xerox_emp varchar
select @xerox_emp = "N"
declare @swexpire int
declare @LoginExpInt int
select @swexpire=value from master.dbo.sysconfigures
where name = 'systemwide password expiration'
print "Serverwide password expire: %1!" ,@swexpire
select distinct @@servername AS "ServeName" , l.suid SybSuid, l.name SybLoginName , @xerox_emp AS "EMPID",
case a.int_value when null then @swexpire else a.int_value end AS PasswordExpInt,
l.pwdate AS "PwdLastChgDate",
DATEADD(DAY, case a.int_value when null then @swexpire else a.int_value end , l.pwdate) AS "PasswordExpDate",
LoginLocked = case when l.lockreason >=0 then "YES" else "No" end,
LoginExpired = case when l.status =4 then "YES" else "No" end
INTO tempdb.dbo.SYBASE_EXPIRED_LOGINS
from master.dbo.syslogins l , master.dbo.sysattributes a
where l.suid *= a.object
and a.object_type='PS'
and a.attribute=0
and object_cinfo='login'
order by l.suid asc
go
ALTER TABLE tempdb.dbo.SYBASE_EXPIRED_LOGINS add NumDaysToExp INT DEFAULT (0);
GO
Update tempdb.dbo.SYBASE_EXPIRED_LOGINS
Set NumDaysToExp = Case when PasswordExpInt !=0 Then DATEDIFF(DAY, getdate(), PasswordExpDate) Else 0 end
--Set NumDaysToExp = Case when PasswordExpInt !=0 Then DATEDIFF(DAY, PasswordExpDate, getdate()) Else 0 end
Query 1 Output Sybase Expired Logins
ServeName,SybSuid,SybLoginName,EMPID,PasswordExpInt,PwdLastChgDate,PasswordExpDate,LoginLocked,LoginExpired,NumDaysToExp
'OLTP_DEV',1,'sa','N',0,2010-03-11 18:44:51.713,2010-03-11 18:44:51.713,'YES','No',0
'OLTP_DEV',2,'probe','N',0,2009-12-05 14:32:47.686,2009-12-05 14:32:47.686,'No','No',0
'OLTP_DEV',38,'TPCS','N',0,2005-12-20 10:10:00.0,2005-12-20 10:10:00.0,'No','No',0
'OLTP_DEV',40,'alvarte','N',90,2013-04-18 16:05:53.33,2013-07-17 16:05:53.33,'No','No',-447
'OLTP_DEV',41,'brownra','N',90,2003-04-28 08:23:00.0,2003-07-27 08:23:00.0,'No','No',-4090
'OLTP_DEV',43,'carutji','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124
SQL Query 2 - Creates Table - Sybase_USER_ROLES
DECLARE @LastSuidCode INT,
@LastRoleDesc VARCHAR(512)
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
SELECT distinct slsuid, slname, ssrname
into tempdb.dbo.Sybase_SECURITY_USERS_AUD_ROLE
FROM tempdb.dbo.Sybase_USER_ROLES
GROUP BY slsuid HAVING ssrname=(MAX(ssrname))
Query 2 - Sybase_USER_ROLES
slsuid,slname,ssrname
1,'sa','oper_role,'
1,'sa','oper_role,replication_role,'
54,'prasaap','mon_role,'
54,'prasaap','mon_role,navigator_role,'
54,'prasaap','mon_role,navigator_role,oper_role,'
54,'prasaap','mon_role,navigator_role,oper_role,sa_role,'
56,'sburdett','dtm_tm_role,'
56,'sburdett','dtm_tm_role,ha_role,'
56,'sburdett','dtm_tm_role,ha_role,mon_role,'
56,'sburdett','dtm_tm_role,ha_role,mon_role,navigator_role,oper_role,'
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,