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.


You can also decide to make a database a public one. Generally this is a very bad idea, as you allow anyone from the "building" to access your "office" or better in database terminology - anyone from the server, so any login is allowed to use your database. To make a database accessible for everyone you need to create a guest user in the database:

USE MyDb
sp_adduser guest

And again, to remove guest user access you should issue:

USE MyDb
sp_dropuser guest