Create login in SQL Server

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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.