SQL Server Login

SQL Server CREATE USER Statement

The SQL CREATE USER Statement creates a database user.

Syntax:
CREATE USER user_name FOR login_name;

Example:
CREATE USER U1 FOR LOGIN TutorialRide

Note: It is necessary to create login before creating a database user.

SQL Server DROP USER statement

Syntax:
DROP USER user_name;  

SQL Server CREATE LOGIN

  • The CREATE LOGIN statement creates an identity, which is used to connect to a SQL Server instance.
  • So before creating a user in SQL Server, it is necessary to create login first.
The four types of Login created in SQL Server are:

1. Login using Windows Authentication

Syntax

CREATE LOGIN [login_name]
FROM WINDOWS
[WITH DEFAULT_DATABASE = database_name | DEFAULT_LANGUAGE = language_name];


Example

Create LOGI
N using Windows Authentication
CREATE LOGIN [test_domain\ TutorialRide]
FROM WINDOWS;


2. Login using SQL Server Authentication

Syntax

CREATE LOGIN login_name
WITH PASSWORD = {'password' | hashed_password HASHED}[MUST_CHANGE]
[SID = sid_value
| DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name
| CHECK_EXPIRATION = {ON| OFF}
| CHECK_POLICY = {ON| OFF}
| CREDENTIAL= credential_name];


Example

Create LOGIN using SQL Server Authentication
CREATE LOGIN TutorialRide
WITH PASSWORD = 'pwd1234';


The above example will create a new login 'TutorialRide', which uses a SQL Server authentication and has a password of 'pwd1234'.

If user wants to change the password for the first time can be modified as:

CREATE LOGIN TutorialRide
WITH PASSWORD = 'pwd1234' MUST_CHANGE,
CHECK_EXPIRATION = ON;


Note: The MUST_CHANGE option is used to change the password on the first login and MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.  

3. Login from a certificate

Syntax

CREATE LOGIN login_name
FROM CERTIFICATE certificate_name;


Example

Create Login from a certificate
CREATE LOGIN TutorialRide
FROM CERTIFICATE certificate1;


In the above example a new login 'TutorialRide' is created that uses a certificate1.
  

4. Login from an asymmetric key

Syntax

CREATE LOGIN login_name
FROM ASYMMETRIC KEY asym_key_name;


Create Login from an asymmetric key

Example

CREATE LOGIN TutorialRide
FROM ASYMMETRIC KEY asym_key1;

SQL Server ALTER LOGIN

ALTER LOGIN login_name
[ENABLE| DISABLE]
PASSWORD = 'password' | hashed_password HASHED
[OLD_PASSWORD = 'oldpassword']
| MUST_CHANG |UNLOCK
| DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name
| NAME = new_login_name
| CHECK_EXPIRATION = {ON| OFF}
CREDENTIAL = credential_name
| NO CREDENTIAL
| ADD CREDENTIAL = credential_name
| NO CREDENTIAL
ADD CREDENTIAL  credential_name
| DROP CREDENTIAL credential_name


Example 1 : Change login using ALTER LOGIN Statement.

ALTER LOGIN TutorialRide
WITH PASSWORD = 'education';


The above statement can change the password of this login to 'tutorial'.

Example 2: DISABLE login using ALTER LOGIN Statement.

ALTER LOGIN TutorialRide Disable;

Example 3: Enable a login using ALTER LOGIN Statement.

ALTER LOGIN TutorialRide Enable;

Example 4: Unlock a login using ALTER LOGIN Statement

ALTER LOGIN TutorialRide
WITH PASSWORD = 'education'
UNLOCK;


Example 5: Rename a login using ALTER LOGIN Statement

ALTER LOGIN TutorialRide
WITH NAME = 'learn';

SQL Server DROP LOGIN Statement

Syntax:
DROP LOGIN login_name;

Note: User cannot drop a login when it is currently logged into SQL Server.

Example:
DROP LOGIN TutorialRide
▲