SQL Logins

An SQL Login is an account, not a user. Logins are given access to the SQL Server, while further down in the process, users must be granted access to databases and database objects in order to perform their functions within SQL Server.

When you install SQL Server, one login is created, called sa (System Administrator). The sa login has access to all databases, objects and functions, and is able to perform configuration tasks.

There is a stored procedure available to you to create new logins:
sp_addlogin @loginame='newlogin', @passwd='newpassword'

The password parameter is optional, though highly recommended. Other optional parameters when you create a user include:

• @defdb - Default database. If the login has a default database, SQL Server will set it as the active database when the user logs in. If not, then the active database will be the master database.
• @deflanguage - Default language for the login.
• @sid - Security Identification Number. SQL Server will allocate a unique number if you don't specify one. Useful if you're moving users to another server and you want them to have the same SID.
• @encryptopt - When the password is stored in the system tables, this parameter tells SQL Server whether to store it in an encrypted format. If this is NULL, then encryption is used by default. If the value of the parameter is skip_encryption, then the password is stored as an ordinary value. For upgrades, you can specify skip_encryption_old, which tells SQL Server that the password was encrypted by a previous version of SQL Server.

sp_droplogin is another stored procedure - this one removes logins. The only parameter is @loginname, i.e. the login you want to delete.

sp_password lets you change the password for a login:
EXEC sp_password 'oldpassword', 'newpassword', 'loginname'
You can only use sp_password to change passwords for logins that you added using the sp_addlogin stored procedure. If you added a login via Enterprise Manager, you'll have to change the password there too!

sp_helplogins - with no parameters, this will give you details of all the logins. Or, you can say sp_helplogins 'loginname' and get details about one particular login.

NT Logins

If you choose to use NT Authentication, you can define NT logins to SQL Server. Then users only have to log on once.

So, here are some more stored procedures you need to know about. Make sure you don't just read this stuff! Get your Query Analyser up and try them out!

• sp_grantlogin - EXEC sp_grantlogin 'Domain\NTlogin'
• sp_denylogin - prevents an NT login connecting to SQL Server - EXEC sp_denylogin 'Domain\NTLogin'
• sp_revokelogin - reverses the change you made with sp_grantlogin or sp_denylogin. Unlike sp_denylogin, sp_revoke login doesn't necessarily prevent a user connecting to SQL Server. If they belong to an NT Group which has access, they can still connect.

Another two generic stored procedures that you can use are sp_defaultdb and sp_defaultlanguage, which set the default database and language respectively. If you use these on NT logins that have not yet been defined to SQL Server, then either of these stored procedures will add them when they execute.