Lesson 1: Adding Login Accounts | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

To use a database on SQL Server, a user first connects to the server, using a login account. A login account can be

The user's Windows NT user account or the account of any group of which the user is a member

A SQL Server login account that you create

A default SQL Server login account

Logins are stored in the master database syslogins system table.

After this lesson, you will be able to

Describe default login accounts
Grant Windows NT users and groups access to SQL Server
Describe and create SQL Server logins

Estimated lesson time: 30 minutes

Default Databases

When a login is added to SQL Server, it often is assigned a default database. Assigning a default database to a login account sets the default context for actions that the user takes; it does not give the user access to the database. As with any database, to gain access to the assigned default database, the user must be granted access, be a member of a Windows NT group that has been granted access, or be a member of a SQL Server role that has been granted access. If the default guest user account exists in the database, it can be used to gain access to the assigned default database. If you do not assign a default database, the default database will be the master database.

Default Login Accounts

SQL Server has two default login accounts: sa and BUILTIN\Administrators.

System administrator (sa) is a special SQL Server login that has all rights on the SQL Server and in all databases.

BUILTIN\Administrators is provided as the default Windows NT login account for all Windows NT administrators. It has all rights on the SQL Server and in all databases.

Granting a Windows NT Account Access to SQL Server

If a user connects to SQL Server using a Windows NT_authenticated login, that user is represented by her own Windows NT user account and the accounts of all Windows NT groups of which she is a member. You can use SQL Server Enterprise Manager or the sp_grantlogin system stored procedure to allow a Windows NT user or group account to connect to SQL Server. Only system or security administrators can grant access to Windows NT users or groups.

Exercise: Using SQL Server Enterprise Manager to Grant Access Rights

In this exercise, you will use SQL Server Enterprise Manager to grant SQL Server access to existing Windows NT user and group accounts. Use the data from the following table when performing the exercise.

Name	Default database
STUDYSQL\Paul	StudyNwind
STUDYSQL\Customer_mgmt	StudyNwind

To grant a Windows NT user or group access to SQL Server

Repeat the following steps for each login:

Expand your server group and then expand your server in SQL Server Enterprise Manager.

Expand Security, right-click Logins, and then click New Login.

Click Windows NT Authentication.

Select the domain where the accounts reside.

Enter the Microsoft Windows NT account name to add after the domain name in the Name field.

Select StudyNwind as the default database; leave the default language as is.

Click OK to create the login.

(For a script that adds the users in this table, see C:\Sqladmin\Exercise\Ch11 \Ntlogin.sql. To add the users with this script instead of using SQL Server Enterprise Manager and the following instructions, open the script file in SQL Server Query Analyzer and execute it.)

NOTE
If you receive an error message stating, "The login name has not been granted access to the default database and therefore will not be able to gain access to the default database. Continue?"; click OK. You will grant access to this database later in this chapter.

Using sp_grantlogin to Add a Windows NT Login to SQL Server

The syntax for the sp_grantlogin statement is as follows:

 sp_grantlogin 'login'

The following example uses the sp_grant_login procedure to add the Windows NT user Paul as a SQL Server login.

 sp_grantlogin 'STUDYSQL\Paul'

The login parameter is the name of the Windows NT user or group to be added. The Windows NT user or group must be qualified with a Windows NT domain name. The limit for combined domain and user or group names is 128 characters.

Consider the following facts and guidelines about adding Windows NT logins to SQL Server:

Because SQL Server has a single login for a Windows NT group, no changes to SQL Server are required when membership in a Windows NT group changes. This prevents orphaned objects (objects that are owned by a user who no longer exists in SQL Server), as long as you do not drop the group.

Deleting a Windows NT group or user from the NT domain in User Manager for Domains does not drop that group or user from SQL Server. This prevents orphaned objects (objects whose owners have been deleted from SQL Server).

When you remove Windows NT users or groups, you should first remove them from Windows NT in order to disallow network access. Then use sp_changeobjectowner to change the owner of objects owned by the account you wish to drop. Finally, remove the login from SQL Server.

Add a login account for a Windows NT group account if every member of the group will be connecting to the SQL Server.

Add a login account for a Windows NT user account only if the user is not a member of a group that can be granted permission collectively.

Although users log on to SQL Server as members of Windows NT groups, SQL Server still knows the identities of the users. The SUSER_SNAME function returns users' domain and login names when users are members of a Windows NT group.

The following table lists other system stored procedures that you can use for managing Windows NT login accounts.

System stored procedure	Description
sp_revokelogin	Removes the login entries for a Windows NT user or group from SQL Server
sp_denylogin	Prevents a Windows NT user or group from connecting to SQL Server

NOTE
Users can change their own passwords at any time by using the sp_password system stored procedure. System administrators can change any user's password using SQL Server Enterprise Manager or by using sp_password with NULL as the old password.

Adding a SQL Server Login

You can use SQL Server Enterprise Manager or the sp_addlogin system stored procedure to create a SQL Server login. Only system or security administrators can create SQL Server logins.

Exercise: Adding a SQL Server Login Using SQL Server Enterprise Manager

In this exercise, you will use SQL Server Enterprise Manager to add three SQL Server login accounts. Use the data from the following table when creating the logins. (For a script that adds the users listed in the following table, see C:\Sqladmin\Exercise\Ch11\Sqllogin.sql, installed on your hard disk drive from the Supplemental Course Materials CD-ROM. To add the users with this script instead of using SQL Server Enterprise Manager and the following instructions, open the script file in SQL Server Query Analyzer and execute it.)

Name	Password	Default database
Carl	Password	StudyNwind
Cathy	Password	StudyNwind
Umberto	Password	StudyNwind

To use SQL Server Enterprise Manager to add SQL Server login accounts

Repeat the following steps for each login:

Expand your server group and then expand your server in SQL Server Enterprise Manager.

Expand Security, right-click Logins, and then click New Login.

Enter the name of the new SQL Server login.

Click SQL Server Authentication.

Enter the password.

Select StudyNwind as the default database; leave the default language as is.

Click OK to create the login.

NOTE
If you receive an error message stating, "The login name has not been granted access to the default database and therefore will not be able to gain access to the default database. Continue?"; click OK. You will grant access to this database later in this chapter.

Using sp_addlogin to Add SQL Server Logins

The syntax for the sp_addlogin statement is as follows:

 sp_addlogin 'login' [, 'password' [, 'database']]

The following example creates the login Carl with the password of password in the StudyNwind database.

 sp_addlogin 'Carl' , 'password' , 'StudyNwind'

The sp_addlogin system stored procedure adds a record to the syslogins table of the master database. After sp_addlogin is executed, the user can log on to SQL Server with that account.

SQL Server logins and passwords can contain up to 128 characters, including letters, symbols, and digits. However, logins cannot

Contain a backslash character (\) other than the one required to delimit the domain name of a Windows NT login

Be a reserved login account—for example, sa or public—or an existing login account

Be NULL or an empty string (' ')

Lesson Summary

In this lesson you learned that when users connect to SQL Server they use a login account, which can be their Windows NT user or group account or a login account added directly to SQL Server. If using a Windows NT account, the account must be granted access to the SQL Server.