SQL Server Create user sp_addlogin

Creates a new Microsoft®sql Server™ login that enables users to connect to an instance of SQL Server that uses SQL Server authentication. Grammar:

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

Parameters @loginame =] ' Login '

The name of the login. The data type of login is sysname, and there is no default setting.

[@passwd =] ' Password '

Login password. The password data type is sysnameand is set to NULL by default. After the sp_addlogin is executed, thepassword is encrypted and stored in the system table.

[@defdb =] ' Database '

Log in to the default database (the database that the login is connected to after logging in). database has a data type of sysnameand is set to masterby default.

[@deflanguage =] ' language '

The default language that is assigned by the system when a user logs on to SQL Server. The language data type is sysnameand is set to NULL by default. If languageis not specified, then language is set to the current default language of the server ( sp_configure configuration variable default language definition). Changing the default language of the server does not change the default language for existing logins. language remains the same as the default language used to add logins.

[@sid =] Sid

Security identification Number (SID). The Sid has a data type of varbinary, which is set to NULL by default. If the Sid is NULL, the system generates a SID for the new login. Although using the varbinary data type, a non-NULL value must be exactly 16 bytes long and cannot exist beforehand. SIDs are useful, for example, if you are writing SQL Server logon scripts, or if you want to move SQL Server logins from one server to another, and you want logins to have the same SID between servers.

[@encryptopt =] ' encryption_option '

Specifies whether the password is encrypted when the password is stored in the system table. The data type of encryption_option is varchar, which can be one of the following values.

value	Description
Null	Encrypt the password. This is the default setting.
Skip_encryption	The password is encrypted. SQL Server should store the value and not encrypt it again.
Skip_encryption_old	The supplied password was encrypted by an earlier version of SQL Server. SQL Server should store the value and not encrypt it again. This option is for upgrade use only.

Return code value

0 (Success) or 1 (failed)

Comments

SQL Server logins and passwords can contain between 1 and 128 characters, including any letters, symbols, and numbers. However, the login cannot:

1. Contains a backslash (\).
2. is a reserved login name, such as sa or public, or already exists.
3. Is null, or is an empty string (').

If you provide the name of the default database, you can connect to the specified database without executing the USE statement. However, you cannot use the default database until the database owner (using sp_adduser or sp_addrolemember) or sp_addrole grants access to the database.

The SID number is the only Microsoft Windows NT® user identification number. Each user's SID number must be guaranteed to be unique within the 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.

When you add logins to SQL Server, it is useful to use skip_encryption to suppress password encryption if the password is already in the form of encryption. If this password is encrypted by a previous version of SQL Server, use skip_encryption_old.

sp_addlogincannot be executed from a user-defined transaction.

The following table shows several stored procedures that are used with sp_addlogin .

Stored Procedures	Description
sp_grantlogin	Add a Windows NT user or group.
Sp_password	Change the user password.
Sp_defaultdb	Change the user's default database.
Sp_defaultlanguage	Change the user's default language.

Permissions

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

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

The following example creates a SQL Server login for user Victoria , without specifying a password or a default database.

EXEC sp_addlogin ‘Victoria‘

B. Creating a login ID and default database

This example creates a SQL Server login for the user Albert and specifies the password food and the default database named corporate .

EXEC sp_addlogin ‘Albert‘, ‘food‘, ‘corporate‘

C. Creating a login ID that uses a different default language

The following example creates a SQL Server login for 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. Creating a login ID with a specific SID

The following example creates a SQL Server login for user Michael with a password of "chocolate", the default database is pubs, and the default language is Us_english,sid 0x0123456789abcdef0123456789abcdef.

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

E. Creating a login ID and not encrypting the password

The following example creates a SQL Server login with a password of "Rose" for user Margaret on Server1 , then the encrypted password, and then logs the user in using the previously encrypted password Margaret Add to Server2, but do not encrypt this password further. User Margaret can then log in to Server2using the password "Rose".

-- Server1EXEC sp_addlogin Margaret, Rose--ResultsNew login created.-- Extract encrypted password for MargaretSELECT CONVERT(VARBINARY(32), password)   FROM syslogins    WHERE name = ‘Margaret‘--Results------------------------------------------------------------------ 0x2131214A212B57304F5A552A3D513453(1 row(s) affected)-- Server2EXEC sp_addlogin ‘Margaret‘, 0x2131214A212B57304F5A552A3D513453,    @encryptopt = ‘skip_encryption‘

SQL Server Create user sp_addlogin