Creating new login. Locking and unlocking the login

To gain access to the Sybase, you need to have a valid login. To create a login for your application type:

sp_addlogin myAppLogin, 'Passw0rd', MyDb, null, 'Application ABC', null, 8, 3

The parameters of the commands are:

• name of the login
• the password
• the name of a default database (warning: if you create a login with the default database, the user in the database is not created automatically. This step needs to be done manually)
• default language
• the description of the account
• time (in days) after that the password will expire. Leave null to have a never expiring password
• number of chars that the password should have. This is important for future password changes that will be done by the login
• number of failed logins, after which the account will be locked

After the login has been created you can test in syslogins whether the information about a new login should be available.

SELECT * FROM syslogins WHERE name = 'myAppLogin'
--date when the password was set
--last login date
--logincount
--create date
--lock date
--lock reason (=2 for automatic lock after crossing the threshold of failed logins)

After this you could start a new session giving the new login name and password. Connection should suceed. Following commands will end with success, displaying the name of the login or the list of databases on the system:

SELECT SUSER_NAME()
GO
sp_helpdb
GO

however the command:

USE MyDb
GO

ends with error:
Could not execute statement.
Server user id 8 is not a valid user in database 'MyDb' SQLCODE=10351 Server=SAP01, Severity Level=14, State=1, Transaction State=1, Line=1 Line 1
USE MyDb

After a couple of failed logins you can observe the increasing logincount in syslogins. This field contains the number of failed attempts and when the predefined treshold is met, the account will be locked. At that time the value will not be increased anymore. The value will be resetted after unlocking the login.

The list of locked logins can be displayed using

sp_locklogin

When you decided to unlock a locked account use command:

sp_locklogin myAppLogin, "unlock"

If you want to lock the login manually use command:

sp_locklogin myAppLogin, "lock"

Having a login, next steps of security configuration can be  done, like creating users in databases, where this login should have access.