sp_addlogin

Creates a new Microsoft® SQL Server™ login that allows a user to connect to an instance of SQL Server using SQL Server Authentication.

Syntax

sp_addlogin [ @loginame = ] 'login'
    [ , [ @passwd = ] 'password' ]
    [ , [ @defdb = ] 'database' ]
    [ , [ @deflanguage = ] 'language' ]
    [ , [ @sid = ] sid ]
    [ , [ @encryptopt = ] 'encryption_option' ]

Arguments

[@loginame =] 'login'

Is the name of the login. login is sysname, with no default.

[@passwd =] 'password'

Is the login password. password is sysname, with a default of NULL. After sp_addlogin has been executed, the password is encrypted and stored in the system tables.

[@defdb =] 'database'

Is the default database of the login (the database the login is connected to after logging in). database is sysname, with a default of master.

[@deflanguage =] 'language'

Is the default language assigned when a user logs on to SQL Server. language is sysname, with a default of NULL. If language is not specified, language is set to the server's current default language (defined by the sp_configure configuration variable default language). Changing the server's default language does not change the default language for existing logins. language remains the same as the default language used when the login was added.

[@sid =] sid

Is the security identification number (SID). sid is varbinary(16), with a default of NULL. If sid is NULL, the system generates a SID for the new login.  Despite the use of a varbinary data type, values other than NULL must be exactly 16 bytes in length, and must not already exist. SID is useful, for example, when you are scripting or moving SQL Server logins from one server to another and you want the logins to have the same SID between servers.

[@encryptopt =] 'encryption_option'

Specifies whether the password is encrypted when stored in the system tables. encryption_option is varchar(20), and can be one of these values.

Return Code Values

0 (success) or 1 (failure)

Remarks

SQL Server logins and passwords can contain from 1 through 128 characters, including letters, symbols, and numbers. However, logins cannot:

• Contain a backslash (\).
  
  
• Be a reserved login name, for example sa or public, or already exist.
  
  
• Be NULL or an empty string ('').

If the name of a default database is supplied, you can connect to the specified database without executing the USE statement. However, you cannot use the default database until given access to that database by the database owner (using sp_adduser or sp_addrolemember) or sp_addrole.

The SID number is the unique Microsoft Windows NT® user identification number. The SID is guaranteed to unique for each user in a Windows NT domain. SQL Server automatically uses the Windows NT SID to identify Windows NT users and groups, and generates a SID for SQL Server logins.

Using skip_encryption to suppress password encryption is useful if the password is already in encrypted form when the login is added to SQL Server. If the password was encrypted by a previous version of SQL Server, use skip_encryption_old.

sp_addlogin cannot be executed from within a user-defined transaction.

This table shows several stored procedures used in conjunction with sp_addlogin.

Permissions

Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlogin.

Examples

A. Create a login ID with no password and master default database

This example creates an SQL Server login for the user Victoria, without specifying a password or default database.

EXEC sp_addlogin 'Victoria'

B. Create a login ID and default database

This example creates a SQL Server login for the user Albert, with a password of food and a default database of corporate.

EXEC sp_addlogin 'Albert', 'food', 'corporate'

C. Create a login ID with a different default language

This example creates an SQL Server login for the user Claire Picard, with a password of caniche, a default database of public_db, and a default language of French.

EXEC sp_addlogin 'Claire Picard', 'caniche', 'public_db', 'french'

D. Create a login ID with a specific SID

This example creates an SQL Server login for the user Michael, with a password of chocolate, a default database of pubs, a default language of us_english, and an SID of 0x0123456789ABCDEF0123456789ABCDEF.

EXEC sp_addlogin 'Michael', 'chocolate', 'pubs', 'us_english', 0x0123456789ABCDEF0123456789ABCDEF

E. Create a login ID and do not encrypt the password

This example creates an SQL Server login for the user Margaret with a password of Rose on Server1, extracts the encrypted password, and then adds the login for the user Margaret to Server2 using the previously encrypted password but does not further encrypt the password. User Margaret can then log on to Server2 using the password Rose.

-- Server1
EXEC sp_addlogin Margaret, Rose

--Results
New login created.

-- Extract encrypted password for Margaret
SELECT CONVERT(VARBINARY(32), password)
   FROM syslogins 
   WHERE name = 'Margaret'

--Results
------------------------------------------------------------------ 
0x2131214A212B57304F5A552A3D513453

(1 row(s) affected)

-- Server2
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453, 
   @encryptopt = 'skip_encryption'

See Also

sp_addrole

sp_addrolemember

sp_adduser

sp_defaultdb

sp_defaultlanguage

sp_droplogin

sp_grantlogin

sp_helpuser

sp_password

sp_revokelogin

xp_logininfo