Defining logins is only the first step in setting up security for SQL Server. If you wish a user to not only be able to connect to SQL Server, but also to access a database, you need to define the login as a user of the desired database.

There are two important stored procedures you should know:
sp_grantdbaccess and sp_revokedbaccess.

Before you use sp_grantdbaccess, you must switch to the database in question, and run the command from there. There is also an equivalent stored procedure called sp_adduser; this is available for backward compatibility.

sp_revokedbaccess can't be used if the user owns objects in the database. There is also sp_drop_user, available for backward compatibility.

Why not go away and try these procedures. Here are a couple of examples:

use mydatabase
go
exec sp_grantdbaccess @loginname = 'mylogin', @name_in_db = 'myusername'
go

sp_revokedbaccess @name_in_db = 'myusername'

Try adding a database object as the user in question, then trying to revoke the access for that user. What error message do you get?

You can also give a login access to a database by ALIASING the login to an existing user rather than adding a new user:

sp_addalias @loginname = 'mylogin', @name_in_db = 'myaliasuser'

Again, this functionality is available for backward compatibility. You're recommended to use ROLES rather than aliases with SQL Server 7. We'll look at roles in the next lesson.

Here's another thing for you to try:

As well as users you add to the database, there are two special users you can have in your database. One is 'guest'. If you add 'guest' as a user for your database, then any login can access the database, whether they've been added as a user or not - they simply use the 'guest' user if they do not have explicit access.

Secondly, there is a 'dbo', or database owner, user. Initially, this dbo is the login that created the database. You can change it, if you wish, using the sp_changedbowner stored procedures.