Create login in SQL Server

A login is a security check process to authenticate the user and make their data secure. In case of SQL, we need to login to connect to the server. Creating login for the server gives a security advantage. Also, security implications are to be understood and kept in mind while creating a login. A user will be granted the access of the database after user login is provided.

Here, we are going to use various methods.

Method 1: login with Password

We are going to use simply the userid and password to login to the server.

Syntax

Create login <login_id> with Password = '<Enter_Password>' ;
<login_id> is the id(user id) used for login.
<Enter Password> is the password used for login.

Example

In this example, we are going to create a login for a particular user using a specific password.

Code

Create login Amrendra with password = 'mypassword';#using userid and password to login

Method 2:login with password that must be changed.

We are going to change the password after connecting to the server for the first time.

Syntax

Create login <login_id> with Password = '<Enter_Password>';
must_change , check_expiration = ON ;

Here, after login, we are using must_change so as to tell that the password must be changed. Also, we have to check the expiration that must be ON in order to change the password.

Example

In this example, we are going to create a login for a user by assigning a password, then to change it.

Algorithm

• Step 1 − Create login using userid

• Step 2 − Assign a strong password

• Step 3 − Use must_change to get the password changed.

• Step 4 − Check for expiration,it must be ON.

Code

Create login Amrendra with password = 'mypassword';#using userid and password to login
must_change , check_expiration=ON;#used to change password

Method 3: login from a Windows domain account.

We are going to create a login from windows domain account and are going to use the domain id with the login id to be more specific and safe for login.

Syntax

Create login [<domainid>\<loginid>] from windows;

Example

In this example, we are going to see how to use a domain id to create a login.

Code

Create login [AP\Amrendra] from windows;#login using domain and login ids 
   from windows

Method 4: login from a SID(security identifier )

We are going to create a login from a security identifier so as to login to the server.

Syntax

Create login <login_id> with Password = '<Enter_Password>',SID= 0C911X22159BGGD851C0E33757EC2BYYYY;

Here,

• <login_id> is the id(user id) used for login.

• <Enter Password> is the password used for login.

• SID can be extracted by using SELECT statement.

Example

In this example, we are going to extract the sid and then perform the login operations using sid.

Algorithm

• Step 1 − Create a login using login_id and password

• Step 2 − Select id, sid from the server

• Step 3 − Drop/delete the login

• Step 4 − Recreate a login using login_id, password and sid.

Code

Create login Amrendra with password = 'mypassword';#created normal login
Select id,sid from sys.sql_login where id='Amrendra';#extracted the  
   sid(0C911X22159BGGD851C0E33757EC2BYYYY) from the server
drop login Amrendra;#deleted the login
Create login Amrendra with password='mypassword',
SID=0C911X22159BGGD851C0E33757EC2BYYYY; #login redone with the help  of extracted sid

Method 5: login with multiple arguments

We are going to create a login in case of multiple arguments together.

Syntax

Create login <login_id> with Password = '<Enter_Password>' ,
Default_database = <mydatabasename>,
check_policy=OFF,
check_expiration=OFF;

Here,

• <login_id> is the id(user id) used for login.

• <Enter Password> is the password used for login.

• <mydatabasename> is the name of database on which we are currently working

• We have to check the policy and expiration which must be OFF so as to proceed with the login.

Example

In this example, we are going to see how to create a login when there are multiple arguments present together.

Algorithm

• Step 1 − Create a login using login_id and password

• Step 2 − Used an argument i.e.default database

• Step 3 − Check policy that should be off

• Step 4 − Check expiration that should be off

Code

Create login Amrendra with Password ='mypassword' ,#created normal login
Default_database = amrendra_data,#default database table is used
check_policy=OFF,#policy must be off
check_expiration=OFF;#expiration must be off

Conclusion

There are five methods provided with respective examples that are being mentioned above. For the first one we have used login id and password to create a login. In the second, we have created a login to change the password. In the third one, we have created login from Windows domain account using domain id also. In the fourth one, we have created a login with SID in order to get access to domain resources. In fifth one, we have created a login for multiple arguments present together.