Authenticating Logins - Special User Identities
(Page 23 of 24 )
In addition to identities mentioned in the previous sections, SQL Server 7.0/2000 has two special cases that need mentioning here: sa and guest.
- The sa login account is the “superuser” account within SQL Server. There is literally nothing it cannot do within the confines of the server. There is only one sa account for each server, and it cannot be deleted or disabled. Furthermore, when a user logs in with the sa account, that user is considered to be every database’s owner. This is the one exception to the rule that only one login account can be assigned ownership to a database. You will explore how sa differs from all the other identities on the system throughout the rest of the book, but for now, just assume that sa can do any and every operation SQL Server supports.
- The guest identity exists both at the login and the database levels. Users that have no other access options can log into the server using the guest login account, which is validated by SQL Server and is different from the Windows NT Guest account. Along the same lines, if a user’s login identity has not been mapped to an identity in a database, that user will assume the guest database identity. This situation is similar to “aliasing” mentioned in the last chapter. Multiple users may use the database as guest at the same time.
Both of these special identities pose their own security risks. sa must be protected because of the unlimited permissions it has on the systems, and guest must be managed carefully because it can allow access to the server and its databases without regard to the appropriateness of that access. It turns out that with Windows Only mode turned on, SQL Server completely ignores non-Windows logins, effectively disabling the sa account. Because the sa account does not have a valid Windows SID, there is no way to use the sa account to log in. This is worth highlighting in a Note.
NOTE Using Windows Only mode disables all SQL Server authenticated login accounts, including the sa account.
As a note of caution, if you decide to use only Windows authentication, be sure to assign an account to the sysadmin role (by default, the Windows local Administrators group is a member). The reason is that only sysadmin can perform some operations. If you forget, you can set SQL Server back to Mixed Mode security by setting the following registry variable to 0:
hkey_local_machine\software\microsoft\mssqlserver\ mssqlserver\loginmode
Finally, if you decide to use Mixed Mode, sa is a special account in SQL Server and the other members of the sysadmin role will not be able to read or change its password. Remember to make the password for the sa account a long string of numbers, letters, and special characters, and keep it in a safe place. That way you will always have access to an administrator account if any problems arise.
 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: Summary >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
