Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 6

SQL Server Login and User Security


Yesterday you learned how to create tables and how SQL Server datatypes are used. You also learned how to create your own datatypes. When you were creating those tables, SQL Server authenticated your connection and user access to a database. Today you examine how that authentication works, starting with Windows NT and working your way down to the database level. You see that Windows NT and SQL Server login security, as well as user accounts, protect your server and database from unauthorized connections.

The SQL Server Security Access Model

Connecting to SQL Server 6.5 has so far been a relatively straightforward thing to do. You've been using SQL Server Standard security, logging in with the SA login. As you connect, a number of things are happening that may not be obvious.

By default there are three different places that security is checked as you attempt to connect from a client program to SQL Server (see Figure 6.1). You may be validated by Windows NT, SQL Server itself (in the form of a login), and then at the individual database level.

Figure 6.1. Network and SQL Server security authentication layers.

Windows NT Authentication

When you connect from your client computer to the Windows NT computer running SQL Server 6.5, Windows NT may require validation of your network connection--this is dependent on your network library. If you are using Named Pipes or Multi-protocol as your network library, you must be validated as an authorized Windows NT connection before you are allowed to talk to SQL Server.

As you can see in Figure 6.2, both Named Pipes and Multi-protocol pass through the Windows NT Server Service, which performs network validation of a user's connection request. Hence, you must have a valid set of Windows NT security credentials to connect to the server computer. Because the TCP/IP Sockets network library doesn't go through the Server Service, you would not need a valid Windows NT account to connect to SQL Server 6.5.

Figure 6.2. SQL Server network communications.


NOTE: As a troubleshooting tool, understanding this security architecture can be useful. If you get the infamous

Unable to connect: SQL Server is unavailable or does not exist.
Specified SQL Server not found.

message, it could be that you are being denied permission to connect to the Windows NT computer upon which SQL Server is installed. To see if this the problem, create a share on the SQL Server computer and attempt to connect to the server (or attempt to connect to an existing share if one exists). If you are unable to connect, or are prompted for a user ID/password, you won't be able to connect to SQL Server. To verify for certain that this is a problem, finish that connection to the aforementioned share, and then try to connect again to SQL Server.

If you are then able to connect, you need to modify the security settings on your Windows NT computer. You can do that in one of three ways:

If you enable the guest account, you have compromised Windows NT security to an extent. To keep your system secure, create an individual account for every user.

The last option presented here, to join a domain, is the preferred option. However, because of the way Windows NT security works on a network, this option is available to you if joining a domain is impossible.

This option is the best option available to you. If it's possible on your network you should use this option. Consult with your network administrator or a book on Windows NT security to examine Windows NT domains and security in more detail.


SQL Server Login

You must provide a valid SQL Server login name and password to connect to SQL Server. You see the detail on how to do this shortly. If your login credentials are valid, you are connected to SQL Server. If your credentials are invalid, you are denied access--even if Windows NT authentication (your network connection to the server) succeeded.

SQL Server Database Username

To use each database on your system, you must explicitly be allowed to enter each database. There are a variety of ways to get into a database, all of which are discussed next.

The Security Modes of SQL Server

SQL Server 6.5 provides three different security modes--Standard, Integrated, and Mixed. The security mode determines whether Windows NT or SQL Server is responsible for validating connection requests. This validation is completely independent of Windows NT network connection authentication, which you examined previously.

Standard Security

Standard security means that SQL Server, not Windows NT, validates users' logins (with the exception of having Named Pipes and Multi-protocol validating connections at the network level). Standard security is set up by default during SQL Server setup. To implement Standard security, simply start using SQL Server 6.5. To verify your current security configuration, you can examine the Security Options tab in SQL Enterprise Manager. To do this, right-click your server name and select Configure from the context menu. The Server Configuration/Options window opens; select the Security Options tab. You should see something similar to Figure 6.3.

Figure 6.3. Security options for SQL Server 6.5.

Notice that the Login Security Mode is set to Standard. You do not change any other entry in this frame yet, as they apply to the Integrated and Mixed security environments. However, the next frame, Audit Level, applies to all security modes. If you want to have events entered into your Windows NT Application Event Log when a login succeeds or fails, you would check the appropriate box here.

After you have selected auditing changes, you must stop and restart the MSSQLServer service in order for those changes to take effect. After you have enabled both successful and failed logins, you see entries in both your SQL Server error log and the Windows NT application event log.

To view the SQL Server error log, select the Server menu and the Error Log from the SQL Enterprise Manager. If you scroll down to the bottom of the current error log, you see the new auditing records (see Figure 6.4). Failed logins are also audited.

Figure 6.4. The SQL Server error log with auditing entries.

These events can also be seen with the Event Viewer application. Click Start  |  Programs  |  Administrative Tools  |  Event Viewer to start the event viewer program (see Figure 6.5). In the event viewer, confirm that you are working with the Application log. Choose Application from the Log menu. You see successful logins entered with a gold key, and failed login attempts have a gray padlock next to them. This makes it easy to discern failed login attempts.

You might find yourself overwhelmed with the amount of information that gets audited over time, particularly successful logins, so you may want to just audit failed logins. This depends on the security requirements of the company for which you work. You might also have problems with filling up the event log. To increase the amount of log records that can be saved, choose Log Settings from the Log menu (see Figure 6.6).

Figure 6.5. Windows NT application event log.

Figure 6.6. The Windows NT Event Viewer Log Settings dialog box.

You can use the arrows next to the Maximum Log Size dialog box to increase the log's size to 2048KB (2MB). This should prevent your application log from filling up quickly and does not take an excessive amount of space on your server. If you choose to continue auditing successful logins, you may want to increase the maximum log size even more.

Logins

Now that you have the proper auditing level set, examine how logins function with SQL Server. For the sake of this discussion, you should assume that the Windows NT network validation portion of connecting to your SQL Server machine is successful. You now must provide a valid SQL Server login name and password to connect to your server. So far, you have been using the special login name SA.

Installed Logins

When you install SQL Server 6.5, there are four predefined logins:

Passwords

Standard security passwords are kept in the syslogins table's password column in the master database. To look at the entries in the syslogins table, start ISQL/w and run this query (you must be logged in as SA to run this):

SELECT  suid, substring(dbname,1,10) AS dbname,
        substring(name,1,10) AS name,
        password, language
        FROM syslogins
suid     dbname    name            password          language
----     ------    ----            ---------         ---------
1    	      master    sa              (null)            (null)
10       master    probe           (null)            (null)
16382    master    repl_publisher  !1!D3$9*#H6&*A*   (null)
16383    master    repl_subscriber !1!J+3IH596!Y9    (null)

The SA and probe login IDs are installed with null passwords. The repl_publisher and repl_subscriber logins are installed with encrypted passwords. All passwords are kept here. If the password is null, you see the null value in the password column. If a password is anything other than null, it is stored as encrypted text.

Passwords that are viewable with a query may seem a bit disconcerting at first. There are a few things to consider before worrying too much. First, only the SA login can view the password column. No other user can view this unless you explicitly give him or her the right to do so (Day 7, "User Permissions"). Second, the encryption algorithm is a one-way algorithm. After a password is encrypted, it cannot be decrypted. When you log in, the password you provide is encrypted and then compared to the encrypted password in the syslogins table. If they match, access is granted to the server. If they don't match, you get the Login Failed error message.

Administering Logins

Creating logins is the first step in setting up your server to allow others access. You can add logins with the sp_addlogin system stored procedure, or through SQL Enterprise Manager.

sp_addlogin login_id [,password [,defdb [,deflanguage [,login_suid]]]]

Where

To add a login to your server, open the ISQL/w utility and log in as SA. Run the following Transact-SQL command:

sp_addlogin yourname, yourpassword

If you run the query against the syslogins table again, you see a new row with your name and an encrypted password. If you create a new connection to SQL Server, you can log in with the name and password that you have just added.

The next thing you may want to do is change your password. You can accomplish this either through the SQL Enterprise Manager or use the sp_password stored procedure.

sp_password old, new [, login_name]

Where

You should change your passwords regularly. Unfortunately, SQL Server 6.5 does not have any way to enforce password restrictions and other security precautions. This is one reason you might choose to implement Integrated security.

Another thing you might like to change is the default database, or the default language with which a user has logged in. This can be done from the SQL Enterprise Manager or with the sp_defaultdb and sp_defaultlanguage stored procedures.

sp_defaultdb login_name, defdb
sp_defaultlanguage login_name [, language]

The parameters are the same as previously discussed. These options simply allow you to change various fields in the syslogins table (the default database, and/or the default language).

There are two additional stored procedures that can be used to manage logins: sp_helplogins and sp_droplogin.

sp_helplogins allows you to get a report on the logins that have been created on your server. A sample run of this stored procedure is shown in Figure 6.7.

Figure 6.7. The results of sp_helplogins.

The other stored procedure, sp_droplogin, removes the login entry from the syslogins table. After an entry is deleted, the user can no longer log in.

sp_droplogin login_name

You have learned how to accomplish login management using the various stored procedures. Now take a look at the SQL Enterprise Manager. Connect to your server and expand the Logins folder as shown in Figure 6.8.

Figure 6.8. The Logins folder of Enterprise Manager.

You can see all the logins you have created on your server. To create a new login, you can select Logins from the Manage menu, or right-click the Logins folder, and select New Login from the context menu. Add your login name, password, and default language as you did with the sp_addlogin stored procedure as shown in Figure 6.9.

Figure 6.9. The Manage Logins dialog box.

After you have filled in the appropriate information, click Add, which becomes available after you've typed in a login name. If you enter a password, you are presented with a Confirm Password dialog box. Retype your password, then select OK to complete the addition. You see your login, with 10 stars in the Password field (see Figure 6.10). You see this regardless of whether you entered a password, and regardless of the length of the password. You learn more about the check in the Default box of the master database later.

Figure 6.10. The Manage Logins dialog box with user information.

To change the default language (the equivalent of the sp_defaultlanguage stored procedure), simply select the appropriate language from the Default Language drop-down box. To change your password (the sp_password stored procedure), simply replace the stars with your new password. You can't change the default database at this time. You learn how to do this in a bit.

Integrated Security

Integrated security is the process of having Windows NT validate all connections to SQL Server. With Integrated security, anything you type in a login dialog box is ignored, and your Windows NT network security credentials are checked. This is done by the Windows NT Server Service as described previously, so it does require that you use the Named Pipes or Multi-protocol network library to communicate with SQL Server. No other network protocols are allowed. In fact, after you've set up Integrated security, the setup program's user interface no longer allows you to pick anything except these two protocols.

You might try thinking of Integrated security in these terms: When you set up Integrated security, you are fundamentally deciding who puts entries into the syslogins table in the master database--you or Windows NT. With Integrated security, you assign Windows NT users to Windows NT groups, and then assign those groups the ability to log in to SQL Server. You can either map those groups to a single login name within SQL Server, or each Windows NT user can have a separate SQL Server login. Either way, there is an entry in the syslogins table in the master database for each login you allow into SQL Server. The difference is whether you put it there as SA with the sp_addlogin system stored procedure, or whether it was put there using the Integrated security functionality of Windows NT and SQL Server together. If Windows NT puts the login into SQL Server, you will not know the password. Windows NT must say it's okay in order for you to successfully access SQL Server.

If that seems a little confusing, it's because Integrated security is more difficult to understand in the beginning. After you get the hang of it, it's the better choice. The easiest way to understand Integrated security is to implement it; try configuring it now. As you walk through the steps, it should make more sense to you.

Setting Up Windows NT Groups

The first step to configuring Integrated security isn't a SQL Server step at all--it's a visit to the User Manager for Domains (or User Manager) utility. Select Start  |  Programs  |  Administrative Tools  |  User Manager for Domains. This starts the User Manager for Domains program shown in Figure 6.11.

Figure 6.11. User Manager for domains.

Your first step to configuring Integrated security? Add new local groups to the Windows NT security accounts manager (SAM) database of the machine that SQL Server is running on. This is a Windows NT internal database, not a SQL Server database. The User Manager for Domains's title bar tells you which domain's (or computer's) security database you are viewing. You need to view the security database of the SQL Server computer. Just to be sure you are looking at the right security database, select User  |  Select Domain. Enter two backslashes (\\), followed by the name of your computer (see Figure 6.12). If your computer is a primary or backup domain controller, the name of the domain is appropriate, as the domain database is the local security database. For the most part, the computer name is correct.

Figure 6.12. Set the proper security database.

When you select OK after entering your computer name, you see the list of accounts and local groups for the SQL Server computer. If you get an error regarding security, you need to log in to your computer as a Windows NT administrator on the computer running SQL Server, and start again.

Now that you are looking at the right security database, you need to create two new local groups to use with SQL Server. One group will contain users who can log in as SA (those Windows NT users who can fully administer SQL Server), and another group for all other Windows NT accounts that you want to have access to SQL Server. You don't have to differentiate what kind of access they need (except for SA users). You only need to decide who can log in to SQL Server. You determine what the users can do upon their login when you look at database users later today, and Day 7, tomorrow.

The first group is for SA access to SQL Server. Select User  |  New Local Group from the menu in User Manager for Domains. Fill in the Group Name field as shown in Figure 6.13, then type in a description explaining the group's purpose. You may notice that the Administrator has been added to your group. This does not mean that the Administrator must exist in every group; rather, it simply means you had that user highlighted when you requested User Manager for Domains to create a new group. You can highlight Administrator, and click Remove to remove that account from your group. This means you don't want that user to be able to log in to your SQL Server as SA.

Figure 6.13. Adding a new local group for Windows NT security.

Now you need to put your account into the group as shown in Figure 6.14. Click Add, which is next to the Members box, and you see a list of accounts you can add. The list is made up of the global groups and accounts from your default domain, or just the accounts if your SQL Server computer is not a member of a domain.

If you do not see your computer name in the List Names From pane, select it from the drop-down list (see Figure 6.15). You see your computer name, your default domain, and any trusted domains in the dialog box. If you are using a domain, select your domain account to add to the list rather than creating and adding a duplicate Windows NT account.

Figure 6.14. The Add Users and Groups dialog box.

Figure 6.15. The list of domains.

Now that you're looking at the right list, add your username to the group by selecting your account name, clicking Add, and clicking OK.


NOTE: You should also add the account you used for the SQLExecutive service during setup. The SQLExecutive service requires that it make a trusted connection to SQL Server to function properly. If you don't add the account, and the SQLExecutive can't make a trusted connection to SQL Server, all functionality provided by that service will fail. This includes tasks, alerts, email integration, and replication.

If you did not use an account for the service (you selected the LocalSystem account option), you do not need to do anything special for that account here.


The completed group should look something like Figure 6.16.

Figure 6.16. The completed Local Group dialog box.

Click OK, then repeat the process and create a group called SQLUsers. This group is used to represent non-SA users. This time, however, add a different user or set of users.

Configuring SQL Server for Integrated Security

The next step is to configure the Security Options tab in SQL Enterprise Manager. Start Enterprise Manager and select Server  |  SQL Server  |  Configure to access the Server Configuration/Options window. Click the Security Options tab to access the same screen you configured before for security auditing.

Now, in the login security mode frame, click the Windows NT Integrated option. You must configure the rest of the options on this screen (see Figure 6.17).

Figure 6.17. The Security Options tab.

Configuring Security with the SQL Security Manager

Now that you have the proper Windows NT accounts and groups set up, and have set the Integrated security options set in the SQL Enterprise Manager, you need to tell SQL Server about the Windows NT accounts and set up your security. You do this with the SQL Security Manager utility. You can start it by selecting Start  |  Programs  |  Microsoft SQL Server 6.5  |  SQL Security Manager. Log in using the SQL Server computer name and the SA ID. Select SA Privilege from the View menu. You should see something similar to Figure 6.18. You may need to expand the Administrators tab.

Figure 6.18. The SQL Security Manager.

When you installed SQL Server, the Administrators group was added to the list of users who have SA access to SQL Server using a trusted connection. This allows your SQLExecutive service to make a trusted connection to SQL Server, even when your server is configured for Standard security. It also allows all Windows NT administrators to log in to SQL Server as SA, which may not good.

If you click the "head" icon on the toolbar (or select User Privilege from the View menu), you are shown which Windows NT groups have been granted user authority to log in to SQL Server. There is nothing here. This is just another way of saying that no users have been granted access as non-SA users. Click the head with the computer, or View  |  SA Privilege from the menu to see the list of users with SA access.

Granting Additional Privileges

In order to grant SA privileges to additional users and groups, you need to click the Security  | |  Grant New option from the menu. This gives you a list of the local groups installed on your server. If you want to see accounts in the default domain, you could select that option as well. You should work with local groups to simplify different security settings in environments with multiple installations of SQL Server. Highlight the SQLAdmins group and click Grant. You should see the success message shown in Figure 6.19, along with the SQLAdmins group behind it.

Figure 6.19. Success message from Granting the SQLAdmins group.

Dismiss the dialog box by clicking OK. One other note here--remember that the Administrators group by default has SA access to SQL Server. If you don't want for these users to have that kind of access, and would like to restrict administrative access to SQL Server administrators only, highlight the Administrators group and select Security  |  Revoke from the menu. You are prompted to confirm your selection to revoke SA privileges from this Windows NT group. Select Yes, and you get this message: Permissions Revoked for Administrators. Click OK to dismiss the dialog box and then click Done. Only the SQLAdmins group (and the operating system itself) have permissions to log in to SQL Server.


NOTE: The NTAUTHORITY\SYSTEM account represents the operating system. You need this entry here because the MSSQLServer service is running under the LocalSystem account by default. The LocalSystem account is equivalent to the NTAUTHORITY\SYSTEM entry here.

Now you configure the security for non-SA users. Choose View  |  User Privilege to get back to the non-SA users configuration dialog box. Select Security  |  Grant New to see the Grant User Privilege dialog box as shown in Figure 6.20.

Figure 6.20. The Grant User Privilege dialog box.

The dialog's bottom frame is now available. The Add Login IDs for Group Members box is checked. This means that a SQL Server login is created for each user of the groups you add. This is equivalent to running sp_addlogin for each user. However, Windows NT sets the password and cannot see this password. You can "pool" users together with a single login (discussed next), but it is not recommended. The bottom button, Add Users to Database, does exactly what its name implies. It's recommended that you not use this option. Life will be much easier for you (and much less confusing) if you only use this dialog box to add Integrated logins, not map users into databases.

To finish adding your non-SA users, highlight the SQLUsers group in the Grant Privilege box, then select Grant. Two logins were added to SQL Server for an example group (see Figure 6.21), Ann and Bob. Click Done, then Done again to dismiss these dialog boxes.

Figure 6.21. Successful grant of non-SA users.

You see the SQLUsers group has been added to the non-SA users, who are allowed to log in using trusted connections in Integrated security mode. If you would like to see the members of this group, double-click the SQLUsers entry on your screen. You see the group members enumerated in the Security Manager window.

That's it! You've added Windows NT groups and users so that some users (those in the SQLAdmins group) have SA access to SQL Server, and other users (those in the SQLUsers group) have regular user logins added to SQL Server. Because you don't know what the passwords are, you cannot yet make Standard security connections using these new logins. However, if you use Enterprise Manager or sp_helplogins to examine the logins at this point, you see the new entries.


NOTE: You can also manage Integrated security using Extended Stored Procedures as well as the SQL Security Manager utility. These include

Server that would otherwise be displayed in the Security Options tab of the Server Configuration/Options dialog box in SQL Enterprise Manager.
Extended stored procedures are discussed in detail on Day 15, "Views, Stored Procedures, and Triggers."


Completing the Change to Integrated Security

After the users log off and log back on to Windows NT (to complete their membership in the new SQLAdmins group), and you have changed to Integrated security, the users can log in as SA. Now, stop the MSSQLServer service (right-click your server in SQL Enterprise Manager and select Stop, or use the SQL Service Manager utility to do the same thing), and then restart it. Log off Windows NT, and then log back on again to make your membership in the SQLAdmins group complete.

You should probably change your registration in SQL Enterprise Manager now that you're running in Integrated Security mode. Start Enterprise Manager, right-click your server name, and select Edit from the pop-up menu. You are presented with the Register Server dialog box. Change your Login Information to the Use Trusted Connection option, and select Modify. SQL Server verifies the connection works, and now you can always make a trusted connection using Enterprise Manager.

Verifying Integrated Security Is Enabled

At this point you can verify that you are actually in Integrated Security mode. Start ISQL/w and try to log in as the login you previously added for yourself (or any username, for that matter) using the Use Standard Security option. You are connected, but as SA--regardless of what you typed in the Standard Security dialog box. You can also request a Trusted Connection explicitly. The choice you make is --because you are in Integrated Security mode, you never get a Standard connection. This is because you mapped yourself to SA in the SQL Security Manager earlier. You can also try to log in as another Windows NT user (one of those that you mapped in your SQLUsers group). Again, regardless of what you enter, you are logged in with the appropriate account that you assigned in the Security Manager utility.

Before you look at Mixed security, take a look at a few more functions available in the SQL Security Manager utility. You can examine additional details of a group or a user after you've granted them security access to SQL Server. Click the group or user you want to view, and select Security  |  Account Detail. You should see something similar to Figure 6.22.

Figure 6.22. The Account Detail dialog box in SQL Security Manager.

This dialog box offers a few interesting options. You can turn off the Generate Random Unique Passwords option and set a password for a group. This could be useful, particularly in a Mixed security environment. There is no need to turn this option off for Integrated connections; you want all users to be validated by Windows NT, and you won't have to manage another password.

You could also drop all logins from this group, using the Drop All Logins button. When you do this, the users are still allowed (potentially) to access SQL Server via the Default Login option discussed earlier. Remember that you must create the default login account yourself for this to work.

The other button, Update Logins, enumerates all users in the group and adds logins to SQL Server for any new group members (see Figure 6.23). Remember that adding new users to the SQLUsers group allows the users in to SQL Server, but only with the default login until you update this group.

Figure 6.23. The Account Detail dialog box in SQL Security Manager (for a user).

From this dialog box, you can drop the SQL Server login associated with this Windows NT user, or manage Database Users and Groups. You learn how to manage these later today. However, you manage this level of security entirely inside Enterprise Manager without using the SQL Security Manager utility.

One additional feature supported here should be examined. It doesn't appear important at first, but the Search option is extremely useful. To access this option in the SQL Security Manager, select Search under the Security menu (see Figure 6.24).

Figure 6.24. The Search for Account Information window.

The first and second choices are very similar. They allow you to enter the name of a user or group and find what level of permissions that user has. It also reports on how they got those permissions (which groups they are members of), and whether the user/group is mapped to SA or user access to SQL Server.

The third choice is particularly useful. It allows you to search for Orphan SQL Server IDs (see Figure 6.25). When you drop a user from a Windows NT group that you have mapped to SQL Server, their login is not removed from SQL Server. When this happens, the login is considered orphaned. Similar to when someone is added, you have to manually remove the login. This is not really a security concern because the user cannot make a trusted connection anymore. It's more of a housekeeping chore. The search excludes SA and probe, but does not exclude the repl_publisher and repl_subscriber logins. In Integrated security mode, you probably don't want any other orphans to be in SQL Server. This dialog box allows you to locate and drop them if you want.

Figure 6.25. The Orphaned Login report.

To see this for yourself, delete an account that is a member of the SQLUsers group using User Manager for Domains from your system. For the example here, delete the user named Ann. If you then look at the SQL Server logins from Enterprise Manager, you still see Ann in the list of logins. Switch to the SQL Security Manager utility, and rerun the search for orphaned logins. This time, Ann is added to the report. Highlight Ann and click Drop. You get a report that the login was removed. You also get numerous errors from the stored procedure sp_dropuser, but at this time there is nothing to worry about.

Mixed Security

Mixed security is exactly what its name implies. You use the best of both Standard and Integrated security. Earlier it was mentioned that you could always request a trusted connection, even when configured for Standard security. This was so that the SQLExecutive service and some ODBC connections that require trusted connections will function regardless of the security mode. Mixed security formalizes this, but goes a step further.

Trusted connections are preferred in Mixed security mode, but Standard connections are supported. SQL Server supports a wide variety of network clients, including Macintosh clients and Novell NetWare-based clients. Some of these clients are unable to support trusted connections. Just because you have a few clients who can't use trusted connections doesn't mean you should throw out all the benefits of Integrated security. Mixed security mode is perfect in this situation. If a client can negotiate a trusted connection, it will be made. Even if the user requests a Standard connection, they will get the trusted connection.

For instance, if a user supplies a password but not a login name, they get a trusted connection. If they type their Windows NT username in the login ID field, they also get a trusted connection. SQL Server will only attempt to validate the login name and password using Standard security when a client expressly requests a Standard security connection with a valid Standard security login.

This can be extremely useful for logins from client applications. Consider, for example, what will happen if you attempt to connect from something like a Microsoft Visual Basic application. If you do not specify the login name and password in your connection to SQL Server and the client operating system and network library supports Named Pipes or Multi-protocol, a trusted connection can be used. Therefore, the users only have to log in once--to the network.

To configure Mixed security, you would take all of the same configuration actions previously described for Integrated security connections. However, on the Security Options tab in Enterprise Manager's Server Configuration/Options dialog box, you choose the Mixed option in the Login Security Mode frame. Otherwise, you must make all the same configuration choices you made for Integrated security. Stop and then restart the MSSQLServer service and you are in Mixed security mode.

To test whether Mixed security is working, try logging in with ISQL/w. Request a trusted connection--you are validated by Windows NT and logged in as SA (assuming you are logged in to a member of the SQLAdmins group). Now log out and log in again. This time, request a standard connection, but don't type in any login credentials. You get a trusted connection to your server again. Now, log in for a third time. This time, type in a standard login name and password (if you still have one). You are connected with standard security.

Database Access

After you have configured login security and have established your logins, you can begin to configure access to databases. Having a login to SQL Server does not give you access to any databases in the server. For that, you must have a database username.

Each database has a separate access path, which is stored in the sysusers table in each database. Logins are essentially mapped (here you go again) to a username in each database that the user needs to access. Creating that mapping, or creating a database user in a database, is done either with the sp_adduser system stored procedure or with the SQL Enterprise Manager.

Adding a User to a Database

sp_adduser login_id [, username [, groupname]]

Where:

To drop a user, and hence deny access to a database, run the sp_dropuser system stored procedure or through the SQL Enterprise Manager.

sp_dropuser username

Where username is the user being dropped.

You can run the sp_helpuser system stored procedure to see which users are in your database and to which login they belong.

sp_helpuser [username]

username is optional. A report of all users is produced if you don't specify a username. Otherwise, you get a report for a specific user.

When you create a database, there is one user already present. That user is named DBO, or DataBase Owner. The DBO user is mapped to the SA login by default. When you install SQL Server, the SA login is considered the owner of all databases. If another login were to create a database, that login would be the database owner. There is nothing that the DBO user cannot do within a database. It is nearly as powerful as the SA login, within each database. However, only the SA login has certain system-wide privileges. Permissions are discussed in more detail tomorrow.

Try to create a user in the pubs database. If you've been following along, you should still have a login for user Bob in your server. It doesn't matter how the login got there (Integrated or Standard), as they all look the same inside SQL Server. Start ISQL/w or the SQL Query Tool in Enterprise Manager and run the following T-SQL statements:

USE pubs
EXEC sp_adduser Bob

This adds a new user--Bob--to the pubs database, mapped back to the login ID Bob at the server. You can verify this by running sp_helpuser in the pubs database (see Figure 6.26).

You can see that Bob has been added as a user to the database, with a login name of Bob. Hopefully, you can also see where it would be confusing to use different names here.

It was previously mentioned that a login does not allow you to access any databases, and yet, when you log in, you can access certain databases. You are able to access the master database, the tempdb database, and the pubs database. How is this possible? Because of the guest username.

Figure 6.26. The results of sp_helpuser.

If you were to run sp_adduser guest in a database, it adds a special user account to the database, known as guest. If you look in the results of the previous sp_helpuser query, you notice this guest account. You also see that it is not mapped back to a login. It is a special user, and not subject to normal rules. When a guest user account exists in a database, any login that requests access to the database but does not have a username specifically created in the database, is allowed in as guest. Hence, there is a guest account built in to the pubs, master, and tempdb databases during setup.


NOTE: You cannot remove the guest username from the master or the tempdb databases.

The guest Username

If you log in with another login for which you have not created a specific username in the pubs database, you access the database as guest. If you try to access a database that you don't have a username in and there is no guest username, you get an error message. For example, if you try to use the msdb database (either by selecting it in the DB text box in ISQL/w, or running the T-SQL command Use msdb), you receive a message similar to this:

Msg 916, Level 14, State 1
Server user id 11 is not a valid user in database `msdb'

The error reports that your login is not properly mapped to a user in the msdb database and implies that there is also no guest account. If you were to query the syslogins table in the master database, you would find out that the Server user id xx in the error message is the internal number SQL Server uses to uniquely identify your login in the server.

So far you have discovered two ways to access a database--being mapped as a user, or using the guest username. You can also use an alias.

Adding an Alias

You can add what's known as a database username alias with the sp_addalias system stored procedure. This allows a login to impersonate another database user.

sp_addalias login_name, username

Where

Adding an alias helps streamline security in a database. You can make as many logins aliases as you'd like to a single username in a database. If a login already has a username mapped in the database, they cannot also be made an alias to a user.


NOTE: In other words, you can have at most one mapping from your SQL Server login ID to a user database. You can be a user in the database, the DBO of the database, or aliased as a user in the database.

This database access option is typically used to map logins to the DBO user in a database. When you examine database permissions tomorrow, you see that it's quite nice to be the DBO in a database. However, there can be only one database owner at any time. Because most companies have more than one administrator, they may want more than one user to act as a database owner.

If you should change your mind and decide to drop the alias, you can do that with the sp_dropalias system stored procedure or with the SQL Enterprise Manager.

sp_dropalias login_name

Where login_name is the login name of the user.

Aliases are stored in the sysalternates system table in each database. To get information about alias users, run the sp_helpuser system stored procedure for a specific user to which you have aliased logins. In Figure 6.27, login ID Richard has been aliased to DBO in the pubs database.

Figure 6.27. The results of sp_helpuser DBO in the pubs database.

Changing the Database Owner

You might want to change the owner of an existing database to assign responsibility for a database to a particular DBA. To do this, the login must not exist in the database as a user, nor can they have an alias in the database. Remember that you can have at most ONE mapping from a SQL Server login ID to a username in a database.

To change the owner, run the sp_changedbowner system stored procedure.

sp_changedbowner login_name [, true]

Where

You have examined four distinct ways to access a database after successfully logging into SQL Server. They are checked in the following order:

Database Groups

Groups are the last security mechanism you look at today. Database groups in SQL Server allow you to put multiple users together and then assign permissions to the groups, rather than to individual users.

Every database has a public group, and every user in a database is a member of public group and cannot be removed. When you add a user to a database, that user is a member of the public group. Think of the public group as a shortcut in each database that means "everyone." A user can also be a member of up to ONE other group in addition to public. Even if a user is a member of another group, that user is a member of the public group.

To add a group to a database, use the sp_addgroup system stored procedure.

sp_addgroup groupname

Where groupname is a valid SQL Server name.

After a group has been created, you can assign a user to a group using the sp_changegroup system stored procedure.

sp_changegroup groupname, username

Where

As just stated, a user can be a member of only one group in addition to the public group. When you run sp_changegroup, the username you reference is changed to a member of the new group, regardless of whether they were previously associated with a different group.

If you want to remove a username from all groups, you could run sp_changegroup and reference the public group. If you do this, you need to put the group name in quotation marks.

For example, you could run the following from an ISQL/w window to add two users to a database, create a group, and then assign the users to the group:

exec sp_addlogin mike
exec sp_addlogin nick
go
use pubs
exec sp_adduser mike
exec sp_adduser nick
exec sp_addgroup accounting
exec sp_changegroup `accounting', mike
exec sp_changegroup `accounting', nick
go

To verify that the users are in the group, you can run the sp_helpgroup system stored procedure.

sp_helpgroup [groupname]

Where groupname is optional. If you don't specify, you get a list of all the groups that exist in the database, including public. If you specify groupname as a parameter, you receive a report of all users who are currently members of that group.

Now that you've seen this stored procedure, you can run it twice--once without a parameter, and once for your accounting group, like this:

use pubs
exec sp_helpgroup
exec sp_helpgroup accounting
go

You should see a report that looks something like Figure 6.28.

Figure 6.28. Output from sp_helpgroup and sp_helpgroup accounting.

Now you can change the group membership for Mike. In this case, you want Mike to be back in the Public group only. You could run this stored procedure:

exec sp_changegroup `public', mike

This removes Mike from any group he was in before (in this case, the Accounting group), and place him back in the Public group.

The last thing you might do with a group is to drop it. To do this, run the sp_dropgroup system stored procedure.

sp_dropgroup groupname

Where groupname is the name of the group you want to drop.

You must remove the users from the group before you can drop the group. If you don't, you receive an error (see Figure 6.29):

exec sp_dropgroup accounting
go

Figure 6.29. Error message returned by attempting to drop a group with users in it.

Managing Users with the SQL Enterprise Manager

It's been a while since you've looked at the SQL Enterprise Manager, and that's on purpose. Start the SQL Enterprise Manager if it's not already running.

Working with Groups

Expand the Databases folder, then the Pubs folder, and then the Groups/Users folder. You see the Accounting group that you created, and the Public group.


NOTE: The user interface here makes it appear that a user is no longer a member of a Public group. This is not the best user-interface design. If this could be changed, the Public group should be below the Groups/Users folder, and then all groups and users should be below the Public group--just to reinforce that users are always part of the Public group.

To create a new group, right-click the Groups/Users folder and select New Group from the menu. You are presented with the Manage Groups dialog box (see Figure 6.30). You can also access this dialog box by selecting Manage  |  Groups from the SQL Enterprise Manager menu.

Figure 6.30. The Manage Groups dialog box.

To create a new group, click the Group dialog box, and select New Group from the drop-down list. Type in the new groupname and click Add.

You can also manage group membership from this dialog box. Simply select the group you want to work with, and select Users from the Users and Users in Group pane. Click the Add and Remove buttons to move users into and out of a group.

You can also drop a group here. In fact, there's an advantage here in using Enterprise Manager. If you drop a group that still has users, Enterprise Manager moves the users to the Public group, and then drops the group. When done, click Close to dismiss the dialog box.

Working with Users

When you right-clicked on the Groups  |  Users dialog box (see Figure 6.31), you might have noticed that you could also have selected New User as well. When you select that option, you'd be presented with the Manage Users dialog box. Again, you could get there from the menu by selecting Manage  |  Users.

Figure 6.31. The Manage Users dialog box.

Simply type in a username, select an available login, change the group membership if you'd like, and then click Add. To drop a user, click Drop after you've selected a user from the User Name drop-down list.

Managing SQL Server Logins

Discussion of the Manage Logins dialog box was avoided up to this point because you need to understand users, aliases, and groups before you can use the interface properly. You can pull up this dialog box by selecting Manage  |  Logins from the menu, or right-clicking on the Logins folder in the SQL Enterprise Manager and selecting New Login (see Figure 6.32).

Figure 6.32. The Manage Logins dialog box.

Now that you have the complete picture, you can properly complete this dialog box. Almost every option you've looked at, including adding logins, adding users, setting a default database and language, aliasing users, and changing group membership can be done from a single interface. This is much easier than trying to run the many stored procedures and using the individual dialog boxes you worked with earlier.

To create a new login and allow them access to a database, type in a new login name, then click the Permit box for the database of your choice. The default database option is set to this database. Enterprise Manager assumes that you want to create a user as well, and uses the same name as the login. If you want to use a different name, simply change it here. If you'd like to set up an alias, simply click the Alias box. A drop-down list presents all the valid usernames in a database, so that you can assign them to an existing user's name. If you'd like to assign them to a group, click that box. If there are any groups other than Public in the database, another drop-down list is presented of the groups in the database.

You can set each of these options for every database on your server, and then click Add just as you did when adding a login.

Summary

To access SQL Server data, you must pass through several layers of security. If using Named Pipes or Multi-protocol for your network library, Windows NT validates your connection request at the network level, regardless of the security mode. If in Standard security mode, SQL Server validates your login with the syslogins table in the master database. If in Integrated security mode, Windows NT validates your login request and allows you access to SQL Server. If in Mixed security mode, you are mapped with either a Standard or Integrated connection. Remember that a user can always request a trusted connection, even in Standard security mode.

After you're logged in, you still need either a database username, an alias, or a guest username before you are allowed access to that database. This has nothing at all to do with the rights (or permissions) you have after you are in the database. Rights and permissions are discussed on Day 7.

Q&A

Q What is the difference between Integrated and Mixed security?

A
Integrated security only allows trusted connections, whereas Mixed security also allows Standard connections--if you request one.

Q What network libraries are supported with Integrated security?


A
Named Pipes and Multi-protocol.

Q What security mode is appropriate if you have mostly Windows NT clients, but a few Macintosh computers that need access to SQL Server?


A
Mixed security.

Q Which utility is used to configure Windows NT to SQL Server login mappings?


A
SQL Security Manager.

Workshop

Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience using what you've learned. Try to understand the quiz and exercise answer before continuing on to the next day's lesson. Answers are provided in Appendix B, "Answers."

Quiz

1. How would you revoke the right of Windows NT administrators to log in to SQL Server as SA?

2. How would you enable auditing of failed logins in SQL Server?

3. If you received an error when running sp_addalias for a login, what do you think the likely cause of the problem is?

Exercises

1. Create the following logins in SQL Server. Also, add each login to a user-defined database on your server.

George

Henry

Ida

John


2. Make John the DBO of the database you just referenced. Fix any errors you receive to make this change possible.

3. If you did not already do so, configure your server for Mixed security, setting up the proper Windows NT groups and mappings so that you still have SA access to SQL Server. Also make sure that the account you are using for the SQLExecutive service is a member of the correct Windows NT group.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.