sybase, how to extract login from syslogins with information and pwd expiration

Hi,
In syslogins sybase I able to have all login name, but how do I extract information on wheter the account has pwx expiration enable or not, I can use sp_displaylogin but this will only on individual login
motioneyeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan FranekCommented:
Look into sp_displaylogin source - it seems like pwd expiration can be stored in three places - 2x in sysattributes and 1x in sysconfigures (that is global serverwide pwd expiration)
/* Password expiration interval */
select @passwdexp = int_value 
from master.dbo.sysattributes
where class = @passeclass 
AND attribute = 0 
AND object = @suid
AND object_cinfo = "login"

if @passwdexp is  NULL
select @passwdexp = int_value 
from master.dbo.sysattributes
where class = 27 
AND attribute = 7

if @passwdexp is  NULL
select @passwdexp = value  
from master.dbo.sysconfigures  
where name = "systemwide password expiration"

Open in new window

Jan FranekCommented:
However, be aware, that the code of sp_displaylogin (and the way the pwd expiration is stored in system tables) can be different in different versions of ASE. My code is from ASE 12.5.4.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ak50304Commented:
What version of ASE are you working on ?
motioneyeAuthor Commented:
Hi,
I'm working on ASE 15.0.3.
ak50304Commented:
This query would give you information about each login and their password expiration:-

If object_id('#ankur_ns') IS NOT NULL
DROP TABLE #ankur_ns
GO
Create table #ankur_ns(login varchar(50),pwexp int)
GO
If object_id('#ankur_s') IS NOT NULL
DROP TABLE #ankur_s
GO
Create table #ankur_s(login varchar(50),pwexp int)
GO
declare @passwdexp int
select @passwdexp = value  
from master.dbo.sysconfigures  
where name = "systemwide password expiration"
Insert into #ankur_ns
select name,@passwdexp
from master..syslogins
GO
Insert into #ankur_s
select l.name login,a.int_value PWE
from master.dbo.sysattributes a, syslogins l
Where a.attribute = 0
AND a.object = l.suid
AND a.object_cinfo = "login"
AND class in (Select class from master..sysattributes where object_type = 'PS')
UNION
select suser_name(object) login,a.int_value PWE
from master.dbo.sysattributes a
where class = 27
AND attribute = 7

update #ankur_ns
set #ankur_ns.pwexp = #ankur_s.pwexp
from #ankur_ns,#ankur_s
where #ankur_ns.login = #ankur_s.login
GO

SELECT login LOGIN, pwexp PASSWORD_EXPIRATION FROM #ankur_ns order by 1

DROP TABLE #ankur_ns
DROP TABLE #ankur_s
GO
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.