Authenticating Logins - Creating Login Accounts
(Page 19 of 24 )
You can add a new SQL Server authenticated account to the sysxlogins table using the sp_addlogin stored procedure:
sp_addlogin [@loginame =] 'login'
[,[@passwd =] 'password']
[,[@defdb =] 'database']
[,[@deflanguage =] 'language']
[,[@sid =] 'sid']
[,[@encryptopt =] 'encryption_option']
As you would expect, you can set the account name, the password, the default database the user will use when he logs in, and the default language to use (for example, English, French, or Spanish). Setting this latter option helps SQL Server know which language to use for error and system messages. Application developers can also tailor an application’s messages based on this value as well.
The encryption option determines how to encrypt the password when it is stored in sysxlogins. The option can take one of the following three values:
- NULL (the default): Encrypts the password using SQL Server 7.0’s encryption scheme. See http://www.nextgenss.com/papers/cracking-sql-passwords.pdf for a discussion of this scheme’s weaknesses.
- skip_encryption:Causes the password to be stored in plain, unencrypted text.
- skip_encryption_old: Indicates the password is already encrypted using SQL Server 6.5’s encryption algorithm. The only purpose of this option is to aid in upgrading a server from version 6.5 to 7.0.
Unless you have some really great need to see the passwords, it is highly recommended that you accept the default and encrypt the passwords. For all new logins, you can safely leave this option alone.
Windows authenticated logins use sp_grantlogin instead of sp_addlogin:
sp_grantlogin [@loginame =] 'login'
[@loginame =] 'login'
'login' Is the name of the Windows NT user or group to be added. The Windows NT user or group must be qualified with a Windows NT domain name in the form Domain\ User—for example, NTTEST\FrankB.
The major difference between the two stored procedures is that sp_grantlogin accepts both Windows accounts and Windows group names. If you want to use one of the built-in local groups or accounts, you can specify it using the special keyword 'builtin' instead of a computer name. Local accounts and groups will use the computer name (for example, SS7_NT_SRV\MaggieM), and domain accounts and groups will use the domain name (for example, NTTEST\FrankB).
 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: Sample Commands >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
