SQL Server Logins

[Previous] [Next]

As we said earlier, SQL Server 7 and SQL Server 2000 provide two login modes: integrated security mode in which Windows logins are allowed, and mixed security mode in which both Windows logins and SQL Server standard logins are allowed. Now let's examine the difference between each login type and how you can best exploit Windows 2000.

Standard Security Logins

As we stated before, SQL Server standard security logins have been around a long time. The implementation requires that a login name be passed, along with a password. The login name is compared to any existing logins in the sysxlogins table in the master database. Assuming a match is found, the password supplied by the client is then encrypted and compared to the encrypted password stored in the password column of the sysxlogins table. If a match is found, access is granted to SQL Server. Note that unlike in Windows NT or Windows 2000 passwords are not case sensitive, even if you set up SQL Server with a case-sensitive sort order.

SQL Server standard security logins are assigned SIDs within SQL Server. However, these are not valid Windows SIDs—they are 16-byte GUIDs that are used strictly within the SQL Server environment.

NOTE

---

What's a GUID? A GUID (globally unique identifier) is a 128-bit (16-byte) integer that is virtually guaranteed to be unique in the world across space and time.

Either a standard security login exists in the sysxlogins system table or it does not. Unlike in Windows NT or Windows 2000, the option to suspend or temporarily disable a SQL Server login does not exist. To add a SQL Server login, you can connect to SQL Server as a system administrator (that is, by using the sa login) and then run the following command:

 exec sp_addlogin 'loginname', 'password' 

The login name will be created, and the password you supplied will be encrypted and stored in the sysxlogins table. You can also add a login by using SQL Enterprise Manager. Note that the login name and password will cross the network unencrypted unless you've enabled a network encryption option, as previously discussed.

Although SQL Server standard security logins work fine, they're not the best security mechanism for a number of different reasons. They don't support even basic security options, such as password expiration, minimum password length, and retry lockout. Also, having more than one point of user account administration is expensive and error-prone. If you want any of these features, you should use Integrated Windows authentication, which we'll turn to now. SQL Server standard security is not being enhanced because Windows 2000 provides all the features needed for the best security integration and protection.

Integrated Windows Logins

As stated earlier, the preferred login mode is Windows NT/2000 integrated security mode. In either this mode or mixed security mode, integrated Windows logins are always allowed. An integrated Windows login receives the login credentials from the client's session with SQL Server and then uses SSPI in Windows to verify the user's identity. Windows returns an access token, which is used to determine access to SQL Server.

Even integrated Windows logins must first be registered with SQL Server. When both SQL Server 7.0 and SQL Server 2000 are installed, the local administrator's group is added as a login to SQL Server. You can then add any additional users or groups later.

When a login attempt is made using Windows integrated security, the access token is checked and all SIDs are checked. Then, a search is made through the sysxlogins table for a match with the Deny option set—that is, bit value &1 in the xstatus column. If a match is made and the login right has been denied, the login attempt is refused. If no match is found, the user's primary SID is extracted from the access token and a search looks for a matching SID to gain access that way. If no match is found, all group SIDs are searched to allow access to SQL Server via Windows group membership. This is how members of the local administrators group are allowed to log in initially.

We must take a moment and say a bit more about the Deny option, since it's specific to Integrated Windows logins. An integrated login is in one of three states: granted (access allowed), denied (access prevented), or revoked (removing either of the two previous status options). A deny state for either the user or any group of which the user is a member will always take precedence. Denying login rights to "Everyone" will deny access to everyone regardless of any other right. If you want to remove a specific login right or remove a deny right, you use the revoke command. Each of these operations can be performed via the SQL Enterprise Manager interface, as shown in Figure 6-4.

Figure 6-4. Setting login information in SQL Enterprise Manager.

You can also run the commands described in Table 6-1.

Table 6-1. Setting login information by using SQL.

Request	Command
Grant login rights	exec sp_grantlogin [mydomain\myuserorgroup]
Deny login rights	exec sp_denylogin [mydomain\myuserorgroup]
Revoke an existing login or deny right	exec sp_revokelogin [mydomain\myuserorgroup]

Since you can have access via either a group or individual account, which one will be selected when you log in? Well, you'll always get in with your own Windows SID if a matching row exists (and there are no Deny entries). However, there's no guaranteed order of selection for group SIDs—simply the first match is accepted. This is potentially important because some other settings, such as default database and default language, are set based on login.

Because both individual user accounts and groups are allowed, you can combine these in an effective fashion. For instance, you can create a domain group named SQL Server Accounting Logins in the EXAIR domain, add the appropriate Windows users to that group, and then grant access rights to SQL Server for that group. Start with this command:

 exec sp_grantlogin [EXAIR\SQL Server Accounting Logins] 

The command will add a row to the sysxlogins table for the SID of the SQL Server Accounting Logins group. At this point, any member of that group will be allowed to log in to SQL Server. Let's assume Michael is a member of that group but you need to deny him access to SQL Server but not revoke his group membership. Use this command to deny Michael login rights:

 exec sp_denylogin [EXAIR\Michael] 

This will add an additional row to sysxlogins, specific to Michael's SID, with the Deny flag set. Now, if Michael attempts to log in to SQL Server, he won't be allowed to log in. To allow Michael to log in again, you must revoke the Deny option (with sp_revokelogin) or explicitly grant login rights (with sp_grantlogin).

Troubleshooting Tip

When a user is unable to log in to SQL Server, look up his or her Windows group membership and verify no Deny entries apply in sysxlogins—that's usually the source of the problem.

Fixed Server Roles

In general, rights are always given to database users and not to SQL Server logins. Fixed server roles are one exception. Fixed server roles are a (fixed) set of rights that can be assigned to a SQL Server login. They are assigned to logins rather than database users because the rights assigned by these roles transcend a single database. An example of these rights is the ability to shut down SQL Server. You cannot alter the rights of each role. Fixed server roles include SysAdmin, ServerAdmin, SecurityAdmin, SetupAdmin, ProcessAdmin, DBCreator, DiskAdmin, and BulkAdmin. We'll describe these roles in the following sections. It's important to note that in addition to the rights we'll describe when describing the various roles, each role has the additional right to add other logins to itself.

SysAdmin

The SysAdmin fixed server role represents the system administrator. Logins who are members of this role have unlimited access to SQL Server. In most cases, they are literally above the permissions-checking systems. You should be paranoid about who you permit in the SysAdmin role.

By default, the sa standard security user is a member, as is the BUILTIN\Administrators Local Administrators group. You can modify this list by using the Windows 2000 user/group administration tools, if you have the appropriate privilege. Make sure that the service accounts that you run SQL Server under, as well as the service account for the SQL Server Agent, are members of this role. You can set SQL Server and SQL Server Agent to run as LocalSystem because this account is a member of the Local Administrators group.

ServerAdmin

ServerAdmin role members have limited rights, including the ability to shut down SQL Server. They can shut down SQL Server only by using the SQL Server command SQL SHUTDOWN and are not granted the rights in Windows 2000 to stop SQL Server services. Members of this role can also reset configuration options for SQL Server and configure the full-text service.

SecurityAdmin

Members of the SecurityAdmin role can act as security administrators for SQL Server. They can grant, revoke, and deny integrated Windows logins, as well as add and remove standard security logins. They can also modify additional properties of logins, such as the default database and default language; add or remove linked server logins (that is, connections to remote SQL Server instances); and grant, revoke, or deny the create database permission, which is described in Table 6-2. SQL Server 2000 allows members of this role to reset the passwords of other standard security users also, except members of the SysAdmin role.

SetupAdmin

SetupAdmin role members can add, remove, and configure linked SQL Servers.

ProcessAdmin

ProcessAdmin members can terminate SQL Server processes by using the SQL KILL command. Typically, operations staff has this right so that they can terminate runaway queries, such as massive Cartesian joins.

DBCreator

Members of the DBCreator role can create, alter, or drop any database in SQL Server. They can also back up and restore databases as well as rename them.

DiskAdmin

DiskAdmin role members are allowed to run commands that were compatible with SQL Server 6.x. These include all SQL DISK commands, as well as the ability to add and remove backup devices, such as logical pointers to tape drives and backup locations.

BulkAdmin

The BulkAdmin role is new to SQL Server 2000. Members of this role are allowed to run the SQL BULK INSERT command. This allows a user to load a data file that the SQL Server service has rights to read into a table. The user must also have INSERT rights in the table into which they are loading the file. Since the BULK INSERT operation runs in the context of the SQL Server service, you must have membership in this role. In SQL Server 7, this role does not exist and the BULK INSERT command is restricted to SysAdmin role members only.