Skip to content

sp-addlogin-transact-sql.md

Latest commit

 

History

History
154 lines (104 loc) · 7.62 KB

sp-addlogin-transact-sql.md

File metadata and controls

154 lines (104 loc) · 7.62 KB
title sp_addlogin (Transact-SQL)
description Creates a new SQL Server login that allows a user to connect to a SQL Server instance with SQL Server authentication.
author VanMSFT
ms.author vanto
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_addlogin
sp_addlogin_TSQL
helpviewer_keywords
sp_addlogin
dev_langs
TSQL

sp_addlogin (Transact-SQL)

[!INCLUDE SQL Server]

Creates a new [!INCLUDE ssNoVersion] login that allows a user to connect to an instance of [!INCLUDE ssNoVersion] by using [!INCLUDE ssNoVersion] authentication.

Important

[!INCLUDE ssNoteDepFutureAvoid] Use CREATE LOGIN instead.

Important

[!INCLUDE ssNoteWinAuthentication]

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_addlogin
    [ @loginame = ] N'loginame'
    [ , [ @passwd = ] N'passwd' ]
    [ , [ @defdb = ] N'defdb' ]
    [ , [ @deflanguage = ] N'deflanguage' ]
    [ , [ @sid = ] sid ]
    [ , [ @encryptopt = ] 'encryptopt' ]
[ ; ]

Arguments

[ @loginame = ] N'loginame'

The name of the login. @loginame is sysname, with no default.

[ @passwd = ] N'passwd'

The login password. @passwd is sysname, with a default of NULL.

Important

[!INCLUDE ssNoteStrongPass]

[ @defdb = ] N'defdb'

The default database of the login (the database to which the login is first connected after logging in). @defdb is sysname, with a default of master.

[ @deflanguage = ] N'deflanguage'

The default language of the login. @deflanguage is sysname, with a default of NULL. If @deflanguage isn't specified, the default @deflanguage of the new login is set to the current default language of the server.

[ @sid = ] sid

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 can't already exist. Specifying @sid is useful, for example, when you're scripting or moving [!INCLUDE ssNoVersion] logins from one server to another and you want the logins to have the same SID on different servers.

[ @encryptopt = ] 'encryptopt'

Specifies whether the password is passed in as clear text or as the hash of the clear text password. No encryption takes place. The word "encrypt" is used in this discussion for the sake of backward compatibility. If a clear text password is passed in, it's hashed. The hash is stored. @encryptopt is varchar(20), and can be one of the following values.

Value Description
NULL (default) The password is passed in clear.
skip_encryption The password is already hashed. The [!INCLUDE ssDE] should store the value without rehashing it.
skip_encryption_old The supplied password was hashed by an earlier version of [!INCLUDE ssNoVersion]. The [!INCLUDE ssDE] should store the value without rehashing it. This option is provided for upgrade purposes only.

Return code values

0 (success) or 1 (failure).

Remarks

[!INCLUDE ssNoVersion] logins can contain from 1 to 128 characters, including letters, symbols, and numbers. Logins can't contain a backslash (\); be a reserved login name, for example sa or public, or already exist; or 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 can't use the default database until you're given access to that database by the database owner (by using sp_adduser, sp_addrolemember), or sp_addrole.

The SID number is a GUID that uniquely identifies the login in the server.

Changing the default language of the server doesn't change the default language of existing logins. To change the default language of the server, use sp_configure.

Using skip_encryption to suppress password hashing is useful if the password is already hashed when the login is added to [!INCLUDE ssNoVersion]. If the password was hashed by an earlier version of [!INCLUDE ssNoVersion], use skip_encryption_old.

sp_addlogin can't be executed within a user-defined transaction.

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

Stored procedure Description
sp_grantlogin Adds a Windows user or group.
sp_password Changes the password of a user.
sp_defaultdb Changes the default database of a user.
sp_defaultlanguage Changes the default language of a user.

Permissions

Requires ALTER ANY LOGIN permission.

Examples

A. Create a SQL Server login

The following example creates a [!INCLUDE ssNoVersion] login for the user Victoria, without specifying a default database. Replace <password> with a strong password.

EXECUTE sp_addlogin 'Victoria', '<password>';
GO

B. Create a SQL Server login that has a default database

The following example creates a [!INCLUDE ssNoVersion] login for the user Albert, and a default database of corporate. Replace <password> with a strong password.

EXECUTE sp_addlogin 'Albert', '<password>', 'corporate';
GO

C. Create a SQL Server login that has a different default language

The following example creates a [!INCLUDE ssNoVersion] login for the user TzTodorov, a default database of [!INCLUDE sssampledbobject-md], and a default language of Bulgarian. Replace <password> with a strong password.

EXECUTE sp_addlogin 'TzTodorov', '<password>', 'AdventureWorks2022', N'български';

D. Create a SQL Server login that has a specific SID

The following example creates a [!INCLUDE ssNoVersion] login for the user Michael, a default database of [!INCLUDE sssampledbobject-md], a default language of us_english, and a SID of 0x0123456789ABCDEF0123456789ABCDEF. Replace <password> with a strong password.

EXECUTE sp_addlogin 'Michael', '<password>', 'AdventureWorks2022', 'us_english', 0x0123456789ABCDEF0123456789ABCDEF;

Related content