Granting access to database (user or guest)

It is not enough to create a login to have access to the database. Each login requires additionally a user in the database where the login should have access to.  You can compare it to entering a building and an office in the building. Login allows only to enter the building and some public accessible places (in Sybase those "public places" are master and tempdb databases). To enter an office (user database) you need additionally to have user account.

So to create the user in the database MyDb for a login named myAppLogin you just issue command like this:

USE MyDb
sp_adduser myAppLogin

Starting from now the login myAppLogin can start using the database. The user can be removed using the following code:

USE MyDb
sp_dropuser myAppLogin

What is very important - you cannot remove a user when it is "in use", so there is an active session in the database using this user, however when the session was not found, the user will be removed.

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.

Sybase security fundamentals: logins vs users

Sybase and MS SQL are like an older and younger sisters, so if you know how the access in organized on one of the platforms, you know immediately how it in general works in another database system.
So, starting from beginning:

• To access the Sybase’s server databases you don’t need direct access to operating system where the server is installed, nor the files, where the database’s files are stored. The reason for this is, that Sybase server just listens on requests from clients and then gets data from the system, however this is done by the Sybase processes, not directly by the client.
• To connect to Sybase, you need to have a valid login. This login can be a locally created login, so just user name with password, but this login can be taken from Kerberos, LDAP or PAM. Usually dba’s decide to use the locally defined logins, but using external directory of users have huge advantages.

USE master
GO
SELECT * FROM syslogins

• The login alone is not enough. To access a specific database you need to have a user account as well. The user account is created per database and you can think about this like about an association of a login name with a database. If the login has such association, than is allowed to access the database. We say in such case that the login has a user in the database.