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.
- 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 Beginner WITH PASSWORD = 'gEe@kF0rG##ks';
- 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 Beginner 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. - 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\Beginner] FROM WINDOWS;
- Creating a login from a SID :
Syntax –
CREATE LOGIN <loginname> WITH PASSWORD = '<Password>', SID = 0x241C11948AEEB749B0D22646DB1AXXXX;
Example to create a login from SID.
CREATE LOGIN Beginner WITH PASSWORD = 'gEe@kF0rG##ks', SID = 0x241C11948AEEB749B0D22646DB1AXXXX;
- 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 Beginner WITH PASSWORD = 'gEe@kF0rG##ks' DEFAULT_DATABASE = BeginnerDB, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF ;
Note :
A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.
Contact Us