In this chapter, you examine how to manage user login IDs and database users. A login ID is the name that allows an individual to access SQL Server. For example, sa is the system administrator user login ID. A database user name allows an individual access to a specific database on SQL Server.
NOTE: The difference between a user login ID and a database user name can become confusing. Remember that adding a login allows an individual to log in to the server; it does not allow the individual access to databases except for the login ID's default database. A database user name does not allow an individual access to SQL Server, but is assigned to the login ID to provide access to a specific database. The login ID and the database user name can be the same, and in many cases are.
A login ID (name) allows a user to log in to SQL Server. When SQL Server is first installed, the server adds the SQL Server login IDs described in the following sections.
NOTE: Throughout this chapter, you see references to login ID and login name. The two are one and the same. Many of the system's stored procedures like sp_adduser and sp_dropuser ask for a login ID, while the Enterprise Manager Manage Login dialog box asks for a login name.
The sa login ID is the login ID of the SQL Server system administrator. The sa user has permission to do anything and everything on the server, from creating users and devices to backing up and restoring databases.
CAUTION: Protect the sa login ID. The sa account should be used only by the database administrator! Do not allow developers and users access to SQL Server with the sa login ID.
The probe login ID is used only in standard mode for some SQL Server administrative applications, such as the Performance Monitor, to connect to SQL Server. In integrated security mode, the sa login ID is used.
If your server has been set up to support replication, one or both of the following login IDs will be installed.
The repl_publisher login ID is set up if the server has been configured to handle subscription replication services.
The repl_subscriber login ID is created if the server is set up as a publication server for replication.
CAUTION: Do not use the probe, repl_publisher, or repl_subscriber login IDs for user logins to SQL Server. These login IDs are reserved for SQL Server services.
The type of security mode selected determines how SQL Server login IDs are created and maintained. SQL Server supports three different security modes:
The security mode is selected during SQL Server installation but can be modified at any time. One factor that limits the security mode you can select is the type of network protocol you will be using (refer to Chapter 5, "Planning an Installation or Upgrade," for more details). The following sections look at the different security modes and how they relate to user management.
Standard security is the standard SQL Server login facility inherited from Sybase 4.x systems and implemented in the Microsoft OS/2 versions of SQL Server. An individual logging on to SQL Server must supply a user name and a password that is validated by SQL Server against a system table. Standard security works over all network configurations.
Integrated security takes advantage of Windows NT user security and account mechanisms. SQL Server user management integrates directly with the NT operating system. Users with a valid Windows NT account can log on to SQL Server without supplying a user name and password once the user account has been granted access to SQL Server. Integrated security can be implemented over the following network protocols: named-pipes protocol or multi-protocol.
TIP: In versions of NT SQL Server before version 6.0, only the named-pipes protocol supported integrated security.
Mixed security is the best of both worlds; it is a combination of the integrated and security modes. Users using trusted connections (named-pipes or multi-protocol) can log in using integrated security; users from trusted or nontrusted connections can log in using standard security.
Study Figure 10.1 to get a better idea of the different types of security modes and the options available.
Figure 10.1.
Security modes.
The standard security access mode dates back to the days of Sybase and the Microsoft pre-NT SQL Server. In standard security mode, a login ID is added to SQL Server for a user. The user must then use the login ID name and password to log in to the server. The login ID does not tie into the NT user name and password scheme. Standard logins are used for standard and mixed security modes.
TIP: Understanding how the standard security mode works makes using integrated security logins easier to understand.
To add a user login for standard security, follow these steps using the SQL Server Enterprise Manager:
Figure 10.2.
The Manage Logins dialog box.
The following list describes the different parameters in the Manage Logins dialog box:
CAUTION: SQL Server version 6.x encrypts the password. Previous versions of SQL Server did not encrypt the password, so on previous versions of SQL Server, the sa user can read user passwords by performing a select * from syslogins.
NOTE: Always assign a login ID to a default database; otherwise, users will be in the master database when they log in.
When a new login ID is added to SQL Server, an entry is placed in the syslogins table. Figure 10.3 shows a query displaying several columns from syslogins. The column suid stands for the Server User ID. The suid is a unique number used throughout SQL Server to identify a login ID. When assigning a suid, SQL Server uses the lowest suid available (filling any possible hole left when a login ID is removed); if no numbers are available, the suid number is incremented by one and assigned as the new suid for the login ID.
Figure 10.3.
SQL Query, displaying several columns of the system table syslogins.
TIP: The suid, not the login name, is used internally by SQL Server to uniquely identify a login name.
The command to add a login ID is the system stored procedure sp_addlogin, which has the following syntax:
sp_addlogin login_id [, password [, default database [, default language[,login_suid]]]]
NOTE: The parameter login_suid is a new parameter added for SQL Server 6.5. Use login_suid to remap a new user login ID with a database user name that has become orphaned. An orphaned user name in a database occurs when an entry in the sysusers table of the database contains an entry with a suid that does not have a matching suid in the syslogins table. This can occur when you load a new database from a backup from another server or from an older backup that includes users you have since removed from syslogins. This feature is not the same as using an alias (discussed later in this chapter).
Groups are used in SQL Server to simplify assigning security and permissions to databases objects, such as tables and stored procedures, by logically grouping users together. A group name can be any name you like, as long as the name follows the rules for SQL Server identifiers. You can create groups to represent your business groups. For example, a software development company may have groups for developers, testers, and analysts. SQL Server installs a single default group called public on every database when the database is created.
TIP: Groups are created for a database, not for the entire server. If you end up adding the same groups to every database, you can create the groups in the model database. When a new database is created, the new groups are included in the new database.
To create a group, perform the following steps using the SQL Server Enterprise Manager:
Figure 10.4.
The Manage Groups dialog box.
CAUTION: A user can belong to only one user-defined group at a time and to the system group public. A user always belongs to the group public and cannot be removed from the public group. The only-one-group limitation is a design limitation because groups are stored in a column of the user row in the sysusers table.
Beware: If you use the SQL Server Enterprise Manager to add a user to a group who belongs to another group, the user is removed from the first group and placed in the new group. SQL Server Enterprise Manager does not warn you that the user has been removed from the first group.
When a group is added to a database, a new entry for the group is made in the system table called sysusers, covered in more detail later in this chapter. The system stored procedure to add a group is called sp_addgroup, which has the following syntax:
sp_addgroup groupname
The system stored procedure to change a users group is sp_changegroup, which has the following syntax:
sp_chagegroup groupname, username
Integrated security allows SQL Server to share the same user name and password used for Windows NT and allows the user to bypass the SQL Server login process. Some of the benefits of integrated security are that the user does not have to remember a separate password and user name; when the password changes in NT, the user does not have to change the password in SQL Server.
How does integrated security work? When a user logs in to Windows NT and accesses SQL Server, which has been set up with integrated or mixed security over a trusted connection, the standard SQL Server login process is bypassed. SQL Server obtains the user and password information from the user's NT network security attributes, which are established when the user logs on to Windows NT. Using integrated security allows you to take advantage of Windows NT features such as password aging and login auditing.
TIP: You can set up more than one sa user account with integrated security. All members of the Windows NT Admin group have sa privileges on SQL Server in integrated security mode.
Integrated security requires more NT hands-on experience or working closely with the NT system administrator when setting up user accounts and groups. Setting up integrated security requires a few more steps than setting up standard security, but integrated security offers many benefits. Follow these steps to set up integrated security:
Integrated or mixed security setup is complete. The following sections cover steps 2 and 4 in more detail.
Setting up the server for integrated or mixed security mode requires configuring the server. Take a look at the Security Options tab of the Server Configuration/Options dialog box (see Figure 10.5).
Figure 10.5.
The Security Options tab in the Server Configuration/Options dialog box.
TIP: If you are going to use integrated security, simplify your life and come up with a naming convention that uses valid SQL Server characters. Doing so can prevent possible account conversion problems. The Apply Now button is new in version 6.x. Selecting Apply Now changes the security mode without requiring you to restart the server, which is required in older versions.
The SQL Security Manager is a separate application and not part of the Enterprise Manager. The SQL Security Manager is located in the Microsoft SQL Server 6.0 or 6.5 program group.
NOTE: Only the sa or a member of the sa group can log on to SQL Server using the SQL Security Manager.
Using the SQL Security Manager is similar to using the Enterprise Manager (see Figure 10.6). You can view the Windows NT users and groups that currently have access to SQL Server by using the View option from the SQL Security Manager menu. Two types of privileges are available from the view option: sa and User. All Windows NT administrators have sa privileges with SQL Server. All other users added through the SQL Security Manager belong to the User privilege group.
Figure 10.6.
The SQL Security Manager.
When using the SQL Security Manager, you cannot select individual Windows NT users
to add--instead, Windows NT groups are added. Adding the group adds all the users
in the group to SQL Server. To add a group with user permissions, perform the following
from the SQL Security Manager:
Figure 10.7.
The Grant User Privilege dialog box.
TIP: Most errors are caused by invalid characters in the user name or group.
What happened? If the Add Login ID for Group Members checkbox was selected, the SQL Security Manager uses the Windows NT group and user account information to create SQL Server login IDs for each user in the selected NT group. The selected NT group and user names are added to the selected SQL Server default database. Removing the users from SQL Server is just as easy. Use the SQL Security Manager and, rather than selecting the Grant New option under the Security menu, select Revoke. The Revoke command removes all the users login IDs and the group from SQL Server. The system commands used to add and revoke users for integrated security are the extended stored procedures xp_grantlogin and xp_revokelogin. For more details on these commands and extended stored procedures, see Appendix B.
Following is a simple checklist to use to help set up integrated security:
Once you have created a login ID for a user, you grant the login ID access to various databases by creating a database user for the login ID. A database user must be added and associated to the login ID in every database the login ID has access to, the exception being databases that have the user guest and that make use of aliases.
NOTE: When you create a login name, you automatically add the user to a database by selecting a default database for the user.
When you add a user to a database, you associate the login ID with the database user name and enable the login ID to access the database. There are several ways you can add a user to a database. You can add a login ID to one or more databases when you create the login ID by using the grid in the Manage Logins dialog box. To add the user to a database, check the database in the grid. When the login ID is added, the login ID and user name are added to each of the databases selected. To add a user to a database using the Manage User dialog box, perform the following steps using SQL Server Enterprise Manager:
Figure 10.8.
The Manage Users dialog box.
The following list describes the parameters in the Manage Users dialog box:
The system stored procedure to add a new user to a database is sp_adduser, which has the following syntax:
sp_adduser login_id [, username [, groupname]]
When a new user is added to a database using the SQL Enterprise Manager or sp_adduser,
an entry for the user is made in the sysusers table of the database to which
the user was added. Figure 10.9 shows the results of the a select * from sysusers
query. The column uid stands for User Identifier and shows the unique
numbers within the database that represent the users.
In Figure 10.9, notice that the uid of the group Executives is
16384, which is the starting uid for groups. A group's uid is greater
than or equal to 16384, except for the public group, which always has a
uid of 0. The column gid stands for Group Identifier and
represents the group to which the user belongs. In Figure 10.9, notice that name
Sam_Meyer has a gid of 16384, which corresponds to the uid
16384 of the group Executives. Figure 10.10 shows the relationship between
the suid in syslogins and sysusers.
Figure 10.9.
A SQL query of the sysusers table.
Figure 10.10.
The relationship between syslogins and sysusers.
To remove a user from a database, use the Manage Users dialog box (refer back to Figure 10.8). Select the user to remove and click the Drop button. You are prompted by a confirmation dialog box. Click the OK button to drop the user from the database. The row in the sysusers table for the user is deleted.
The corresponding system stored procedure to drop a user from a database is sp_dropuser, which has the following syntax:
sp_dropuser User_Name
A special user name, guest, can be added to a database to allow anyone with a valid SQL Server login to access the database. The guest user name is a member of the group public. Once a guest user has been added to a database, any individual with a valid SQL Server login--regardless of security mode--can access the database as the guest user. A guest user works as follows:
NOTE: The guest user always has a uid of 2. A guest user is added to the master database and the pubs database when the system is installed. SQL Server version 6.x prevents you from accidentally dropping the guest user from the master database. If you removed guest from the master database, only the sa user could log in to SQL Server! When users log in to SQL Server, they have access to the master database as the guest user. (Don't worry, the guest user has very few permissions in the master database.)
What is an alias? An alias enables you to assign more than one login ID to a specific user name in a database. For example, suppose that you are running a bank and you have a database called BIG_BUCKS. You have a user name in the BIG_BUCKS database called banker. You also have three other SQL Server login IDs: banker1, banker2, and banker3, which perform the same function as the user banker. Instead of adding each login ID to the database, you alias the three users to the database user banker (see Figure 10.11).
In the database BIG_BUCKS, shown in Figure 10.11, a single entry is placed in the sysusers table for the user banker. When the three other users are aliased to the user banker, an entry is made in the database system table sysalternates for each login ID aliased. When one of the alias users tries to access the database, the table sysusers is scanned for the suid of the user. When the suid is not found, the sysalternates table is checked. If suid is found, the column altsuid in sysalternates is used as the suid to search the sysusers table for the correct uid.
Figure 10.11.
An example of an alias.
Aliases are typically used to assign more than one login ID as the DBO (database
owner).
NOTE: The DBO can be assigned only to a single login ID. Using an alias is the only way to allow multiple logins to be DBOs.
TIP: If you use the DUMP and LOAD commands to move databases to a new server, the sysusers table travels with the database. The suids in the new server may not correctly match the suids in the database to the proper users. You can use aliases to remap object permissions in the moved database. SQL Server 6.5 provides an alternative method to using aliases when you use the DUMP and LOAD commands to move a database: a system stored procedure called sp_change_users_login. The topics of mismatched login IDs and database user names, as well as sp_change_users_login, are discussed in "Suggested User Account Management Strategies," later in this chapter.
Aliases can be assigned in several ways. To add an alias from the Manage Logins dialog box, follow these steps:
Figure 10.12.
Adding an alias using the Database Access grid in the Manage Logins dialog box.
To add an alias using the Manage Users dialog box, follow these steps:
Figure 10.13.
Adding an alias using the Manage Users dialog box.
The system stored procedure to add aliases is sp_addalias, which has the following syntax:
sp_addalias login_id, user_name_in_database
CAUTION: Be careful when dropping from a database a user being used as an alias for other login IDs. When the user name is dropped from the database, the alias users lose access to the database.
Login ID, database user, and group information can be viewed in several ways. To see the current login IDs on a server, use the Enterprise Manager and select the server; then click the Logins folder. The user logins for the server are displayed as shown in Figure 10.14.
Figure 10.14.
The SQL Enterprise Manager Logins folder.
To see the current groups and users in a database, select the database and click
the Groups/Users folder; a list of the database's groups is displayed. Click a group
to see the users in that group. Figure 10.15 shows a drill down of groups and users.
Figure 10.15.
The SQL Enterprise Manager Groups/Users folder.
TIP: To see detailed user information, double-click the folder to display the Manage Users dialog box (refer back to Figure 10.8).
To see more detailed information for login IDs, use the Manage Logins dialog box (refer back to Figure 10.2). SQL Server 6.5 includes a new stored procedure called sp_helplogins, which has the following syntax:
sp_helplogins [Login_Name_Pattern]
The sp_helplogins procedure displays information about the current logins such as remote logins and database users and aliases. Output from the sp_helplogins stored procedure is shown in Figure 10.16.
For more detailed group information, use the Manage Groups dialog box (refer back to Figure 10.4). For information about database users or aliases, use the Manage Users dialog box (refer back to Figure 10.8).
The stored procedure sp_helpuser displays user information for a specific user or for all users in a database; the procedure has the following syntax:
sp_helpuser [user_name]
Figure 10.16.
Sample output from the stored procedure sp_helplogins.
Once you have created a login ID, you can easily perform the following tasks:
Performing maintenance tasks requires using the Manage Logins dialog box (see Figure 10.17). The following sections explain how to change a password and drop a login ID.
Figure 10.17.
The Manage Logins dialog box.
To change the password for a login ID, select the login ID you want to change from the Login Name drop-down list box in the Manage Logins dialog box. Enter the new password in the Password text box and click the Modify button. You are prompted by the Confirm Password dialog box (see Figure 10.18).
Figure 10.18.
The Confirm Password dialog box.
Enter the new password and click the OK button. The password has been changed.
NOTE: Users can change their own passwords, but only the sa can change another user's password.
The system stored procedure to change the password is sp_password, which has the following syntax:
sp_password old_password, new_password [, login_id]
CAUTION: I cannot begin to tell you how many "secure environments" I have walked into and then logged on to their secure SQL Server as the sa, using the NULL password. As stated in Chapter 6, "Installing or Upgrading SQL Server," always give the sa account a password. The environment is not secure when anyone who has ever used Microsoft SQL Server or Sybase SQL Server knows your sa password. If you are using integrated security, you do not have to give sa a password; however, if you are using standard or mixed security, please do! One last thing--don't forget the sa password. If you do, and you are using standard security mode, you must reinstall SQL Server.
To remove a login ID, select the login ID you want to change from the Login Name drop-down list box in the Manage Logins dialog box and click the Drop button. When you are prompted by the confirmation dialog box, click the OK button. The login ID is dropped.
TIP: Before you can drop a login ID with the Enterprise Manager, you must first drop and re-create any user-owned objects in the databases using another login ID. When a login ID is dropped using the Enterprise Manager, the login ID is removed from any databases to which it was explicitly granted access.
The system stored procedure to drop a login is sp_droplogin, which has the following syntax:
sp_droplogin Logon_name
CAUTION: This caution applies to users of SQL Server 4.2x. I am happy to say that the caution does not apply to SQL Server 6.x. With SQL Server 4.x, the stored procedure sp_droplogin does not check to see whether the login ID has been removed from all databases or whether it owns any objects. With SQL Server 6.x, sp_droplogin does check to see whether the login ID has been removed from all databases or whether it owns objects. However, I still recommend that you use the Enterprise Manager to drop user login IDs. With SQL Server 4.x, never use sp_droplogin. Always use the SQL Administrator to drop user logins, for two reasons: sp_droplogin does not check to see whether you have removed the user from all the databases. Because sp_droplogin does not check, you can drop a login ID from the system and leave a sysusers entry in another database. When the suid is reused later, the new user can automatically access any database left over from the previous login ID that used the same suid. sp_droplogin does not check to see whether the login ID being dropped owns any database objects. Because sp_droplogin does not check for database objects, it is possible to leave database objects (such as tables and stored procedures) that have no owner. Even worse, these objects will be owned by the next login ID added.
Now that you know all about user management, what about some of the important maintenance and implementation issues: When should you use integrated security or standard security? What about user naming conventions? When should you use aliases? The following sections answers these question and present some suggested strategies to follow for user account management.
If your organization is part of an NT domain or your users log in to a single NT server, consider using integrated security if you are running the proper network protocols. Your users will appreciate a single login name and password, and you can take advantage of login auditing. The downside is that anyone who is a Windows NT system administrator can get SQL Server administrator privileges. Remember that if you are not using the network protocols multi-protocol or named-pipes, you must use standard security.
Come up with a standard convention for your login names, whether it is the user's first and last name, such as John_Doe, or an abbreviation, such as JohnD. Just be consistent.
It is nice that SQL Server allows you to have a different user name than the login ID in a database. However, I find that the database is easier to manage by keeping the two names the same.
Groups are very important to object security. Create groups that make sense. Grouping users by business function is a very good approach.
There are two cases in which I recommend using aliases:
SQL Server 6.5 Alternative to Aliases for Orphaned UsersSQL Server 6.5 comes with a new stored procedure that can be used to correct the missing relationships between the syslogins and sysusers tables. The sp_change_users_login stored procedure can be used to list orphaned database users; it can also be used to reestablish the links between the syslogins and sysusers tables. sp_change_users_login corrects the link problem by adding new login IDs to the syslogins table or by updating the sysusers table with existing suids. The syntax for the command is as follows:
sp_change_users_login {`Auto_Fix' | `Report' | `Update_One'} [,User_Name_Or_Pattern [, Login_Name]]
Use the `Report' option to list the orphaned users in a database. To assign an orphaned user to an existing login ID, use the 'Update_One' option. To reestablish the links between the two tables, use the `Auto_Fix' option. Be warned that when you use `Auto_Fix', you must check the outcome with the `Report' option. `Auto_Fix' may make incorrect assumptions when reestablishing the link between sysusers and syslogins. The output from the execution of the following example is show in Figure 10.19.
sp_change_users_login `Auto_Fix', `Lisa%'
Figure 10.19.
Sample output from the sp_change_users_login stored procedure.
CAUTION: Although sp_change_users_login can correct missing links between the sysusers and syslogins tables, if cannot correct security problems of improperly mapped suids. Suppose that you decided to move a database from Server1 to Server2. On Server1, the DBO has a suid of 11. On Server2, suid 11 is assigned to a general user with limited database access. When you move the database from Server1 to Server2, the general user on Server2 becomes the DBO. sp_change_users_login does not report this type of problem because the suid of the database has a matching suid in the syslogins table.
Following are some important tips and tricks to keep in mind for user management:
You should now have a good understanding of user management in SQL Server and
how and when to use the different security modes. In the next chapter, you learn
about database object permissions and how to assign them to users and groups.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.