How to Create Login and User in MSSQL Server

You can create a login and user in Microsoft SQL Server to grant access to a database. An elementary illustration of how to accomplish this is as follows:

First, create a login.

Step 1: Create a Login

A login is a security principal that allows a user to connect to a SQL Server instance. Use the CREATE LOGIN statement to create a login:

USE master;

GO

-- Change 'YourPassword' to a strong password

 

CREATE LOGIN YourLoginName WITH PASSWORD = 'YourPassword';

 

Replace YourLoginName  and Make sure your password is safe with the desired login name.

Step 2: Create a User in a Database

After creating a login, you need to create a user in a specific database and map it to the login. Use the CREATE USER statement for this purpose:

You must first create a login and then map it to a user in a specific database. Use the CREATE USER statement in this way:

USE YourDatabaseName;

-- Change 'YourDatabaseName' to the desired database

 

CREATE USER YourUserName FOR LOGIN YourLoginName;

 

Step 3: Grant Permissions (Optional)

The GRANT command in Microsoft SQL Server allows you to grant a user particular permissions. The CONTROL permission in a database can be used to grant a user read, write, and owner (or complete control) permissions. To give you an example, consider this:

Permission to Read, Write, and Own

Here's how to give a user read, write, and owner access assuming you have already created a login and a user.

USE YourDatabaseName;

 

-- Change 'YourDatabaseName' to the desired database

 

-- Grant read and write permissions

GRANT SELECT, INSERT, UPDATE, DELETE TO YourUserName;

 

-- Grant ownership (CONTROL) permission

GRANT CONTROL ON SCHEMA::dbo TO YourUserName;

-- Change 'dbo' to the schema name if different

Note: In order to improve security and lower the possibility of unwanted activities, users should only be granted the permissions required for their jobs, sticking to the principle of least privilege.