Open In App

Create login in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

A login is an user account that you can use to access the SQL server. Logins are attached to users by the security identifier (SID).

Permissions to create login :
Users with membership in the security-admin or sysadmin fixed server role can create logins on the server.

  1. Creating a login with a password :

    Syntax –

    CREATE LOGIN <loginname> WITH PASSWORD = '<Password>';
    

    Note : Passwords are case-sensitive.

    Example to create a login for a particular user with password.

    CREATE LOGIN geeks 
    WITH PASSWORD = 'gEe@kF0rG##ks'; 
  2. Creating a login with a password that has got to be changed :

    Syntax –

    CREATE LOGIN <loginname> WITH PASSWORD = '<Password>'
    MUST_CHANGE, CHECK_EXPIRATION = ON;
    

    Example to create a login for a user with password.

    CREATE LOGIN geeks WITH PASSWORD = 'gEe@kF0rG##ks'
    MUST_CHANGE, CHECK_EXPIRATION = ON;
    

    Note –
    The MUST_CHANGE option requires users to change this password the first time they connect to the server. The MUST_CHANGE option can’t be used when CHECK_EXPIRATION is OFF.

  3. Creating a login from a Windows domain account :

    Syntax –

    CREATE LOGIN [<domainname>\<loginname>] 
    FROM WINDOWS;
    

    Example to create a login from a Windows domain account.

    CREATE LOGIN [AD\geeks] FROM WINDOWS; 
  4. Creating a login from a SID :

    Syntax –

    CREATE LOGIN <loginname> 
    WITH PASSWORD = '<Password>', 
    SID = 0x241C11948AEEB749B0D22646DB1AXXXX;
    

    Example to create a login from SID.

    CREATE LOGIN geeks 
    WITH PASSWORD = 'gEe@kF0rG##ks', 
    SID = 0x241C11948AEEB749B0D22646DB1AXXXX; 
  5. Creating a login with multiple arguments

    Syntax –

    CREATE LOGIN <loginname>
    WITH PASSWORD = '<Password>',
    DEFAULT_DATABASE = <Databasename>,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF ; 

    Example to create login using multiple arguments together.

    CREATE LOGIN geeks WITH PASSWORD = 'gEe@kF0rG##ks'
    DEFAULT_DATABASE = GeeksDB,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF ; 

    Note :
    A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.


Last Updated : 18 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads