How to Create a Login and Assign Database Permissions in MSSQL

Quick answer: In SQL Server, create a login at the server level, then map it to a database as a user and grant it a role such as db_datareader or db_datawriter. Do this in SSMS under Security > Logins, or with a few T-SQL statements. Always grant the least privilege the account actually needs.

Create the login (server level)

CREATE LOGIN appuser WITH PASSWORD = 'StrongPass123!';

Or in SSMS: expand Security > Logins, right-click New Login, choose SQL Server authentication, and set a strong password.

Map the login to a database

USE YourDatabase;
CREATE USER appuser FOR LOGIN appuser;

Grant permissions with roles

Assign database roles rather than broad rights:

ALTER ROLE db_datareader ADD MEMBER appuser;   -- read
ALTER ROLE db_datawriter ADD MEMBER appuser;   -- write

Grant db_owner only when the account genuinely needs full control of the database. Avoid using the sa account for applications.

Frequently asked questions

What is the difference between a login and a user?
A login authenticates at the server level; a user grants access inside a specific database. A login is mapped to a user per database.

How do I let the login connect remotely?
Enable SQL Server authentication and open the port - see the guide on remote MSSQL connections.

Managed SQL Server on a SoftSys managed Windows VPS comes with logins and permissions set up securely by our team.