locked login list status in sybase ASE

[Former Member]

Hi all,

i want to list the logins which is locked.

select name from syslogins where status&2=2 ,it will give the all the locked user lists?

if the status is 226 ,224 -->in this what output it will give.

Please help me .

[javier_barthe]

HI,

Have you tried with locksuid is not null filter over syslogins query?

Hope helps.

Regards.

Javier.

[Former Member]

sorry for late reply

i will and post u

[kevin_sherlock]

Yes,

"select name from syslogins where status&2=2"

will list all locked logins.  As for your question, just test it for yourself:

1> select "226&2" = 226 & 2

2>       ,"224&2" = 224 & 2

3> go

226&2       224&2

----------- -----------

           2           0

Obviously, "status" colum is a bitmap.  226 has the "2" bit set (a locked login).  And 224 does not.

226 = 128bit + 64bit + 32bit + 2bit

224 = 128bit + 64bit + 32bit

Decimal

	Hex	Status
2	0x2	Account is locked.
4	0x4	Password has expired. This is set on the user's first login attempt after expiration.
8	0x8	Indicates that the value of exempt inactive lock is set to TRUE. It is not applicable for login profile rows.
16	0x10	OMNI:autoconnect mode is enabled.
32	0x20	May use SAP ASE internal authentication mechanism – syslogins.
64	0x40	May use LDAP external authentication.
128	0x80	May use PAM external authentication.
256	0x100	May use Kerberos external authentication.
512	0x200	Indicates a login profile.
1536	0x200 | 0x400	Indicates a default login profile.
2048	0x800	Indicates an authentication mechanism specified in a login profile.

[former_member89972]

exec sp_locklogin (no arguments) lists out locked logins. Yo do not have to re-invent the wheel 🙂

If you are on *NIX exec sp_displaylogin '%'  pipe and then 'grep' what you need/do not need

If you want to query system table then read the reference manual on Tables and syslogins table in particular.

HTH

Avinash

[Former Member]

how to do sp_display % |grep .please give me example .so that i can understand easily.

And if want to check the 10 login name whether it is locked or not ..how to do it.

sp_locklogin will give you the all locked login details.i want only particular login details.

[former_member89972]

Check the output of sp_displaylogi '%' first.

Then filter out the based on column "Locked" which is 7th column in the output.

For me this works :

isql -Usa -P xxxx -S <SERVER> -w1024

1> exec sp_displaylogin '%'

2> go | grep -v 'NO'

Giving me a quick list of locked logins, with full login details.

You can further add more filters or parse the output by saving it to a file

HTH

Avinash

[former_member89972]

Fix to the suggestion above.

My apologies,  it is 8th column in the output and not 7th !!

This should work for Unix isql

1> sp_displaylogin '%'

2> go | awk '{if ($8 == "YES") print $0;}'

Alternately you can sort the single column output of sp_locklogin and then look for the login in the list.

1>sp_locklogin

2>go | sort

HTH

Avinash

[former_member89972]

And if you need headers in the neat output following will serve the purpose

Basically this looks for header lines and then word "YES" in the 8th "Locked" column.

1> sp_displaylogin '%'

2> go | awk '$8 ~ "YES" || $1 ~ "Suid" || $1 ~ "------"'

HTH

Avinash