Login IDs

To access a SQL Server, a user must be assigned a valid login ID: a unique name that follows the rules for identifiers and is used to identify a user on a SQL Server. A login ID is also sometimes referred to as a login.

The login IDs you need to create depend on the server's login security mode:

• If a server is configured for integrated security with Windows NT, it is not necessary to create a login ID for each user. The SQL Security Manager utility provided with SQL Server can automatically map the Windows NT – based usernames to SQL Server login IDs or to a default login ID. For more information about using SQL Security Manager, see Chapter 9, Managing Security.
• If a server is configured for standard security, you must create a separate login ID for each user who will connect to that SQL Server–except for those users who will always connect as system administrator by using the built-in sa login ID.

  You can create a visitor login ID to be shared by infrequent users who have common access and permission requirements. For more information, see Visitor Login IDs.

• If a server is configured for mixed security, you will need to create login IDs for each user who will connect over nontrusted connections, using the standard security rules described above. You may also want to create a visitor login ID.

  For each user who will connect over trusted connections using integrated security rules, you will need to use SQL Security Manager to map the Windows NT – based usernames to SQL Server login IDs or to a default login ID.

Other information is associated with a login ID. When you create a login ID, you will usually also:

• Provide a password for that login ID.

  The default password is NULL. Unless you assign some other password when a user's login is established, the user will not have a password. When using standard or mixed security, you should not allow a NULL password.

  With integrated security, there is no need to maintain passwords. However, if you change to standard or mixed security, anyone can log in if there are no passwords, so it is a good idea to set random passwords. (SQL Security Manager will do this for you.)

• Assign a default language.

  The user will be automatically logged in as using that language. Only the languages available to the server are available to the user. If no language is specified, SQL Server's default language is used.

• Assign a default database.

  A default database is the database that the user is connected to immediately after logging in to SQL Server. However, before a user can access objects in the default database, the user must be granted permissions for the database.

  If no database is specified, the master database becomes the default database. Although you should have master as the SA's default database, you should assign default databases other than master to other users to discourage them from creating database objects in the master database.

• Specify the databases the login ID will be permitted access to.
• Assign a username (or alias) and, optionally, a group for each database the login ID is permitted access to.

For information about creating a login ID and specifying the above information, see Chapter 9, Managing Security. For information about usernames, aliases, and groups, see About Database Users.

The SA can manage all aspects of login IDs. A user is allowed to change his or her own password, default language, and default database.

If you no longer want a user to have access to a particular SQL Server, you must drop the user's login ID from that server. You cannot drop the login ID of a user who is the owner of the current database or a database object in the current database. You must transfer the ownership of the current database and drop owned objects from the current database before dropping the owner's login ID.

A login ID provides access to a SQL Server but does not allow a user to access any user databases. A database username and appropriate permissions are required to access and use a database. For information about providing database access, see About Database Users and About Permissions.