Create a Login

  • Article
  • ten minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Case yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

This topic describes how to create a login in SQL Server or SQL Database by using SQL Server Management Studio or Transact-SQL. A login is the identity of the person or process that is connecting to an instance of SQL Server.

Background

A login is a security principal, or an entity that can be authenticated past a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows primary (such equally a domain user or a Windows domain group) or you tin can create a login that is not based on a Windows principal (such equally an SQL Server login).

Notation: To use SQL Server Hallmark, the Database Engine must use mixed mode authentication. For more than information, run across Choose an Authentication Mode.

As a security principal, permissions tin exist granted to logins. The telescopic of a login is the whole Database Engine. To connect to a specific database on the example of SQL Server, a login must be mapped to a database user. Permissions inside the database are granted and denied to the database user, non the login. Permissions that have the scope of the whole instance of SQL Server (for example, the CREATE ENDPOINT permission) tin can exist granted to a login.

Annotation: When a login connects to SQL Server the identity is validated at the master database. Utilise independent database users to authenticate SQL Server and SQL Database connections at the database level. When using contained database users a login is not necessary. A independent database is a database that is isolated from other databases and from the case of SQL Server/ SQL Database (and the master database) that hosts the database. SQL Server supports independent database users for both Windows and SQL Server authentication. When using SQL Database, combine independent database users with database level firewall rules. For more information, run into Contained Database Users - Making Your Database Portable.

Security

SQL Server requires ALTER Whatsoever LOGIN or ALTER LOGIN permission on the server.

SQL Database requires membership in the loginmanager role.

Create a login using SSMS

  1. In Object Explorer, expand the binder of the server instance in which y'all want to create the new login.

  2. Right-click the Security folder, point to New, and select Login....

  3. In the Login - New dialog box, on the General page, enter the proper name of a user in the Login name box. Alternately, click Search... to open the Select User or Group dialog box.

    If you click Search...:

    1. Nether Select this object type, click Object Types... to open the Object Types dialog box and select whatsoever or all of the following: Built-in security principals, Groups, and Users. Congenital-in security principals and Users are selected past default. When finished, click OK.

    2. Nether From this location, click Locations... to open the Locations dialog box and select one of the available server locations. When finished, click OK.

    3. Nether Enter the object proper noun to select (examples), enter the user or grouping name that you want to find. For more than data, come across Select Users, Computers, or Groups Dialog Box.

    4. Click Advanced... for more avant-garde search options. For more information, see Select Users, Computers, or Groups Dialog Box - Avant-garde Page.

    5. Click OK.

  4. To create a login based on a Windows principal, select Windows authentication. This is the default selection.

  5. To create a login that is saved on a SQL Server database, select SQL Server hallmark.

    1. In the Countersign box, enter a countersign for the new user. Enter that password once more into the Ostend Countersign box.

    2. When changing an existing password, select Specify old password, and then type the onetime countersign in the Old password box.

    3. To enforce password policy options for complexity and enforcement, select Enforce password policy. For more information, see Password Policy. This is a default option when SQL Server authentication is selected.

    4. To enforce password policy options for expiration, select Enforce countersign expiration. Enforce password policy must be selected to enable this checkbox. This is a default choice when SQL Server hallmark is selected.

    5. To force the user to create a new password afterward the offset time the login is used, select User must modify password at next login. Enforce countersign expiration must exist selected to enable this checkbox. This is a default option when SQL Server authentication is selected.

  6. To associate the login with a stand up-lonely security certificate, select Mapped to certificate and then select the name of an existing certificate from the list.

  7. To associate the login with a stand-alone asymmetric primal, select Mapped to disproportionate key to, and so select the name of an existing key from the list.

  8. To associate the login with a security credential, select the Mapped to Credential check box, and so either select an existing credential from the list or click Add together to create a new credential. To remove a mapping to a security credential from the login, select the credential from Mapped Credentials and click Remove. For more data well-nigh credentials in general, see Credentials (Database Engine).

  9. From the Default database list, select a default database for the login. Master is the default for this choice.

  10. From the Default language listing, select a default language for the login.

  11. Click OK.

Boosted Options

The Login - New dialog box as well offers options on iv additional pages: Server Roles, User Mapping, Securables, and Condition.

Server Roles

The Server Roles folio lists all possible roles that can be assigned to the new login. The post-obit options are available:

bulkadmin check box
Members of the bulkadmin stock-still server role can run the BULK INSERT statement.

dbcreator check box
Members of the dbcreator fixed server office can create, change, drop, and restore any database.

diskadmin check box
Members of the diskadmin fixed server role tin can manage deejay files.

processadmin cheque box
Members of the processadmin fixed server role can finish processes running in an instance of the Database Engine.

public check box
All SQL Server users, groups, and roles belong to the public stock-still server part past default.

securityadmin cheque box
Members of the securityadmin fixed server part manage logins and their backdrop. They can GRANT, DENY, and REVOKE server-level permissions. They tin can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.

serveradmin check box
Members of the serveradmin stock-still server role tin change server-wide configuration options and shut down the server.

setupadmin check box
Members of the setupadmin fixed server role can add together and remove linked servers, and they can execute some system stored procedures.

sysadmin cheque box
Members of the sysadmin fixed server role can perform any activity in the Database Engine.

User Mapping

The User Mapping page lists all possible databases and the database part memberships on those databases that can be applied to the login. The databases selected determine the office memberships that are bachelor for the login. The following options are available on this folio:

Users mapped to this login
Select the databases that this login can access. When yous select a database, its valid database roles are displayed in the Database role membership for: database_name pane.

Map
Permit the login to admission the databases listed below.

Database
Lists the databases bachelor on the server.

User
Specify a database user to map to the login. Past default, the database user has the aforementioned proper noun as the login.

Default Schema
Specifies the default schema of the user. When a user is beginning created, its default schema is dbo. It is possible to specify a default schema that does not yet be. Yous cannot specify a default schema for a user that is mapped to a Windows group, a document, or an asymmetric central.

Guest account enabled for: database_name
Read-just attribute indicating whether the Guest account is enabled on the selected database. Use the Status folio of the Login Properties dialog box of the Guest account to enable or disable the Guest account.

Database office membership for: database_name
Select the roles for the user in the specified database. All users are members of the public role in every database and cannot exist removed. For more data nigh database roles, see Database-Level Roles.

Securables

The Securables folio lists all possible securables and the permissions on those securables that tin can be granted to the login. The following options are available on this page:

Upper Grid
Contains ane or more items for which permissions can be fix. The columns that are displayed in the upper filigree vary depending on the principal or securable.

To add together items to the upper grid:

  1. Click Search.

  2. In the Add Objects dialog box, select one of the following options: Specific objects..., All objects of the types..., or The server server_name. Click OK.

    NOTE: Selecting The server server_name automatically fills the upper filigree with all of that servers' securable objects.

  3. If you lot select Specific objects...:

    1. In the Select Objects dialog box, under Select these object types, click Object Types....

    2. In the Select Object Types dialog box, select any or all of the following object types: Endpoints, Logins, Servers, Availability Groups, and Server roles. Click OK.

    3. Nether Enter the object names to select (examples), click Browse....

    4. In the Browse for Objects dialog box, select any of the bachelor objects of the type that you selected in the Select Object Types dialog box, and and then click OK.

    5. In the Select Objects dialog box, click OK.

  4. If you select All objects of the types..., in the Select Object Types dialog box, select whatever or all of the following object types: Endpoints, Logins, Servers, Availability Groups, and Server roles. Click OK.

Name
The name of each principal or securable that is added to the grid.

Type
Describes the type of each item.

Explicit Tab
Lists the possible permissions for the securable that are selected in the upper grid. Not all options are available for all explicit permissions.

Permissions
The proper name of the permission.

Grantor
The principal that granted the permission.

Grant
Select to grant this permission to the login. Clear to revoke this permission.

With Grant
Reflects the state of the WITH GRANT option for the listed permission. This box is read-only. To apply this permission, use the GRANT statement.

Deny
Select to deny this permission to the login. Clear to revoke this permission.

Condition

The Status page lists some of the authentication and authorization options that tin be configured on the selected SQL Server login.

The following options are bachelor on this page:

Permission to connect to database engine
When you lot piece of work with this setting, y'all should recall of the selected login as a principal that tin can exist granted or denied permission on a securable.

Select Grant to grant CONNECT SQL permission to the login. Select Deny to deny CONNECT SQL to the login.

Login
When you work with this setting, you should think of the selected login every bit a tape in a table. Changes to the values listed here volition be applied to the record.

A login that has been disabled continues to exist as a record. But if it tries to connect to SQL Server, the login will not exist authenticated.

Select this option to enable or disable this login. This selection uses the ALTER LOGIN statement with the either ENABLE or DISABLE option.

SQL Server Hallmark
The check box Login is locked out is merely available if the selected login connects using SQL Server Authentication and the login has been locked out. This setting is read-only. To unlock a login that is locked out, execute Alter LOGIN with the UNLOCK selection.

Create a login using Windows Authentication using T-SQL

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

                      -- Create a login for SQL Server by specifying a server name and a Windows domain account name.    CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;   GO

Create a login using SQL Server Authentication using T-SQL

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

                      -- Creates the user "shcooper" for SQL Server using the security credential "RestrictedFaculty"    -- The user login starts with the password "Baz1nga," simply that countersign must be inverse after the commencement login.    CREATE LOGIN shcooper       WITH PASSWORD = 'Baz1nga' MUST_CHANGE,      CREDENTIAL = RestrictedFaculty;   Go

For more information, run across CREATE LOGIN (Transact-SQL).

Follow Up: Steps to take later you create a login

After creating a login, the login can connect to SQL Server, but does not necessarily have sufficient permission to perform any useful work. The following list provides links to common login actions.

  • To have the login bring together a office, encounter Bring together a Function.

  • To authorize a login to use a database, run into Create a Database User.

  • To grant a permission to a login, see Grant a Permission to a Chief.

Meet Too

Security Centre for SQL Server Database Engine and Azure SQL Database