sp_addlogin System Stored Procedure

For SQL Server 6.5 information, see sp_addlogin in What's New for SQL Server 6.5.

Creates a new SQL Server user by adding an entry to master.dbo.syslogins.

Syntax

sp_addlogin login_id [, passwd [, defdb [, deflanguage]]]

where

login_id

Is the login ID of the user. Login IDs must follow the rules for identifiers.

passwd

Is the user's password. If no password is specified, the default password is NULL (no password needs to be explicitly entered). After sp_addlogin has been executed, the password is encrypted and stored in the syslogins table.

defdb

Specifies the user's default database (the database the user is connected to after logging in). If no defdb is specified, the default is master.

deflanguage

Is the default language assigned when a user logs in to SQL Server. If no deflanguage is specified, the server's default language, defined by the configuration variable default language, is used.

Remarks

After sp_addlogin is executed, the user can log in to SQL Server.

When you are using integrated security, the presence or absence of a login ID determines if a user is uniquely known within SQL Server or is instead mapped to a default account, if one exists. If no default account and no login ID exists for a specific user, that user cannot log in.

The user can change his or her own password at any time by using the sp_password system stored procedure. The system administrator can change any user's password by using sp_password with NULL as the old password.

If the name of a default database is supplied (defdb), the user is connected to the specified database without the USE statement and without passing through master. However, the user cannot use the default database until given access to that database by its database owner (by using the sp_adduser or sp_addalias system stored procedure).

The user can change his or her own default database at any time by using the sp_defaultdb system stored procedure. The system administrator can change any user's default database by using sp_defaultdb.

The user can change his or her own default language at any by using the sp_defaultlanguage system stored procedure. The system administrator can change any user's default language by using sp_defaultlanguage.

Examples

A.    Create a Login ID

This example creates a SQL Server login for the login ID Victoria. Since the passwd and defdb parameters are not included, Victoria's password is NULL and her default database is master.

sp_addlogin Victoria

B.    Create a Login ID and Default Database

This example creates a SQL Server login identification number for the user Albert. His password is food and his default database is corporate.

sp_addlogin Albert, food, corporate

C.    Create a Login ID with a Different Default Language

This example creates a SQL Server login for Claire. Her password is caniche, her default database is public_db, and her default language is French.

sp_addlogin Claire, caniche, public_db, french

Permission

Only the system administrator can use this stored procedure.

Tables Used

master.dbo.sysdatabases, master.dbo.syslogins, master.dbo.syslanguages

See Also