Authenticating Logins - Managing Login Accounts
(Page 18 of 24 )
Having covered the influence the network libraries have on the login process, it is time to look at login account management within SQL Server itself. Table 2-2 shows the definition of the syslogins view, which is the starting point for any discussion of login accounts.
| Column Name | Data Type | Description |
|---|
| suid | smallint | Server user ID |
| sid | varbinary(85) | Security ID |
| status | smallint | For internal use only |
| createdate | datetime | Date the login was added |
| updatedate | datetime | Date the login was updated |
| accdate | datetime | For internal use only |
| totcpu | int | For internal use only |
| totio | int | For internal use only |
| spacelimit | int | For internal use only |
| timelimit | int | For internal use only |
| resultlimit | int | For internal use only |
| name | varchar(30) | Login ID of the user |
| dbname | nvarchar(128) | Name of the user’s default database when connection is established |
| password | nvarchar(128) | Encrypted password of the user (may be NULL) |
| language | nvarchar(128) | User’s default language |
| denylogin | int | 1, if login is a WindowsNT user or group and has been denied access |
| has access | int | 1, if login has been granted access to the server |
| isntname | int | 1 if login is a Windows NT user or group; 0 if the login is a SQL Server login |
| isntgroup | int | 1, if login is a Windows NT group |
| isntuser | int | 1, if login is a Windows NT user |
| sysadmin | int | 1, if login is a member of the sysadmin server role |
| Securityadmin | int | 1, if login is a member of the securityadmin server role |
| Serveradmin | int | 1, if login is a member of the serveradmin fixed server role |
| Setupadmin | int | 1, if login is a member of the setupadmin fixed server role |
| Processadmin | int | 1, if login is a member of the processadmin fixed server role |
| Diskadmin | int | 1, if login is a member of the diskadmin fixed server role |
| Dbcreator | int | 1, if login is a member of the dbcreator fixed server role |
| Loginname | nvarchar(128) | Actual name of the login, which may be different from the login name used by SQL Server |
Table 2-2. Syslogins View
In a change from SQL Server 6.5, syslogins is a view, not a table.
Many of syslogins’s columns are translations of status bits in the xstatus column of the sysxlogins table, and others, such as the SUID column, are computed values based on system functions. SQL Server 7.0 handles generation of the SUID a little differently from SQL Server 6.5, because Microsoft deprecated the use of SUIDs in favor of using Windows NT SIDs.
If you are familiar with Windows NT security, then you already know that each account has an SID. That SID is actually a globally unique identifier (GUID) and is guaranteed to be unique across all accounts in the Windows NT domain or aWindows 2000 forest. SQL Server uses that SID as the identifier for an account in syslogins so that it can distinguish accounts from one another. The benefit of this approach is that servers can share SIDs so that a user can access a database on more than just the local server. For accounts that use SQL Server authentication, the sp_addlogin stored procedure will then generate a GUID for the new account.
SQL Server 7.0 also generates a pseudo-SID for native SQL Server logins that is guaranteed to be unique within the server but has no relevance outside the database server. For database user accounts, SQL Server 7.0 maps the SID to a user ID in the sysusers system table (which I discuss in greater detail in Chapter 4) instead of the SUID, no matter which kind of authentication the login uses. There is an SUID column in sysusers, but it is only there for backward compatibility. In SQL Server 2000, SUIDs disappear from syslogins and sysusers completely. The end result is that in SQL Server 7.0 and later versions, all users in a database will map to an SID that either comes from a domain controller or from SQL Server itself.
NOTE Windows 98 does not support Windows NT–style login protocols; there fore, the desktop version of SQL Server 7.0/2000 only supports SQL Server authenticated logins. This is the only special consideration for the discussion of login security in this chapter.
The login process is quite simple once the user completes the connection to the server. SQL Server compares the account and password presented at login to the entries in the syslogins table. In the example output from the syslogins table shown in Table 2-3, if Jack tries to log in with SQL Server account Jack and the password PailofWater, SQL Server looks for Jack in the name column and then checks to see if the password matches.
| Name | Password |
| guest | NULL |
| Jack | 0x2131214A2130402F49494F46384F3 |
| C380000000000000000000000000000 |
| repl_publisher | 0x2131214433243E392A234836262A4 |
| 12A0000000000000000000000000000 |
| repl_subscriber | 0x2131214A212B26214948353936215 |
| 9390000000000000000000000000000 |
| NTTEST\morris | NULL |
| sa | 0x2131214A212E2458483E3B373B3F2 |
| F3D0000000000000000000000000000 |
| NULL | NULL |
| NULL | NULL |
Table 2-3. Example Output
Note that in the example, the passwords are encrypted, so you’ll have to trust me that Jack’s password is PailofWater and SQL Server does allow him access to the server.
For Windows authenticated logins, the value in the name column will be the full Windows account name, and the password column will be NULL. As you can see in the example, my account, NTTEST\Morris, has a NULL password. What you do not see in the example is the SID column, which holds the Windows security identifier for my account. Why this is significant requires a little explanation.
Whenever Windows NT/2000 authenticates a Windows account, it creates an access token, as described previously. Inside the access token is a list containing the SID for the account and all the SIDs for the groups to which the user belongs. When I log in, SQL Server does not actually look for my account name in syslogins, but instead searches for my account’s SID. If it does not find my account’s SID, it looks for a row containing any one of the SIDs for the groups listed in the access token. If two or more group SIDs have matching rows, then SQL Server pseudo-randomly picks which group to use. In all cases, if my account SID has a matching row, it is the SID used to grant access.
The ability to match any SID in the access token creates some new options for granting server access. Rather than having to grant login permission to every user individually, as you have to do in SQL Server 6.5, it is now possible to grant permission to a group and then add users who should have access to that group. This kind of structure makes it easy to manage server access at the domain level instead of at the level of the individual server. In addition, it makes assigning access rights to multiple servers very easy. You can either create a group for each server and add users to the groups based on which servers they need to use, or you can create groups that represent roles and permit login access to the servers that support those roles. For example, the latter example can be a good choice for web server farms that access multiple database servers to generate their content, because the accounts used by the web servers can all be members of a single group that has login access to the database servers. You will explore these options in greater detail in Chapter 4, but for now, here is a complete list of the options that can be assigned an SID:
- Local accounts on the server running SQL Server
- Local groups on the server running SQL Server
- NT 4.0 domain accounts in the server’s NT 4.0 domain
- NT 4.0 domain global groups in the server’s NT 4.0 domain
- NT 4.0 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain
- Windows 2000 domain accounts in the server’s Windows 2000 domain
- Windows 2000 domain local and global groups in the server’s Windows 2000 domain
- Windows 2000 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain
- Windows 2000 forest universal groups in the server’s forest, if SQL Server is running on a version of Windows 2000 server
 This is from SQL Server Security Distilled, second edition, by Morris Lewis (Apress, ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now. |
Next: Creating Login Accounts >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
