.png)
CREATE LOGIN (Transact-SQL)
Creates a Database Engine login for SQL Server, Windows Azure SQL Database, and SQL Server PDW.
Note
|
|---|
|
The CREATE LOGIN options vary for SQL Server, SQL Database, and SQL Server PDW. |
-- Syntax for SQL Server
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }
<option_list1> ::=
PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]
<option_list2> ::=
SID = sid
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
| CREDENTIAL = credential_name
<sources> ::=
WINDOWS [ WITH <windows_options>[ ,... ] ]
| CERTIFICATE certname
| ASYMMETRIC KEY asym_key_name
<windows_options> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
Passwords are case-sensitive.
Prehashing of passwords is supported only when you are creating SQL Server logins.
If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.
When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.
|
Important
|
|---|
|
CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later. For more information, see Password Policy. |
Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server. You can create a login from a certificate or asymmetric key only when the certificate or asymmetric key already exists in master.
For a script to transfer logins, see How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008.
Creating a login automatically enables the new login and grants the login the server level CONNECT SQL permission.
SQL Database Logins
In SQL Database, the CREATE LOGIN statement must be the only statement in a batch.
In some methods of connecting to SQL Database, such as sqlcmd, you must append the SQL Database server name to the login name in the connection string by using the <login>@<server> notation. For example, if your login is login1 and the fully qualified name of the SQL Database server is servername.database.windows.net, the username parameter of the connection string should be login1@servername. Because the total length of the username parameter is 128 characters, login_name is limited to 127 characters minus the length of the server name. In the example, login_name can only be 117 characters long because servername is 10 characters.
In SQL Database you must be connected to the master database to create a login.
For more information about SQL Database logins, see Managing Databases and Logins in Windows Azure SQL Database.
In SQL Server and SQL Server PDW, requires ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role.
In SQL Database, only the server-level principal login (created by the provisioning process) or members of the loginmanager database role in the master database can create new logins.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server.
After creating a login, the login can connect to the Database Engine, SQL Database, or SQL Server PDW appliance, but only has the permissions granted to the public role. Consider performing the some of the following activities.
-
To connect to a database, create a database user for the login. For more information, see CREATE USER (Transact-SQL).
-
Create a user-defined server role by using CREATE SERVER ROLE (Transact-SQL). Use ALTER SERVER ROLE … ADD MEMBER to add the new login to the user-defined server role. For more information, see CREATE SERVER ROLE (Transact-SQL) and ALTER SERVER ROLE (Transact-SQL).
-
Use sp_addsrvrolemember to add the login to a fixed server role. For more information, see Server-Level Roles and sp_addsrvrolemember (Transact-SQL).
-
Use the GRANT statement, to grant server-level permissions to the new login or to a role containing the login. For more information, see GRANT (Transact-SQL).
A. Creating a login with a password
Applies to all.
The following example creates a login for a particular user and assigns a password.
CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>'; GO
B. Creating a login with a password
Applies to SQL Server and Advanced Data Warehouse.
The following example creates a login for a particular user and assigns a password. The MUST_CHANGE option requires users to change this password the first time they connect to the server.
CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>' MUST_CHANGE; GO
C. Creating a login mapped to a credential
Applies to SQL Server.
The following example creates the login for a particular user, using the user. This login is mapped to the credential.
CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>',
CREDENTIAL = <credentialName>;
GO
D. Creating a login from a certificate
Applies to SQL Server.
The following example creates login for a particular user from a certificate in master.
USE MASTER;
CREATE CERTIFICATE <certificateName>
WITH SUBJECT = '<login_name> certificate in master database',
EXPIRY_DATE = '12/05/2025';
GO
CREATE LOGIN <login_name> FROM CERTIFICATE <certificateName>;
GO
E. Creating a login from a Windows domain account
Applies to SQL Server.
The following example creates a login from a Windows domain account.
CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS; GO
