Skip to content
DBAExperts
SQL Server Users & permissions

How to create a login, a user, and grant permissions in SQL Server (GRANT)

Access in SQL Server has two levels: the login lives on the server and the user lives in the database. Here you create both and grant least-privilege permissions.

  1. 1

    Create the login at the server level

    It can be SQL authentication (with a password) or Windows/Azure AD. Prefer integrated authentication whenever possible.

    -- Autenticacion SQL
CREATE LOGIN [app_user] WITH PASSWORD = N'UnaClaveFuerte#2026',
     CHECK_POLICY = ON;

-- Autenticacion de Windows
-- CREATE LOGIN [DOMINIO\usuario] FROM WINDOWS;
  2. 2

    Create the user inside the database

    The user maps the login into a specific database. Without this step, the login cannot touch the database.

    USE [MiBase];
GO
CREATE USER [app_user] FOR LOGIN [app_user];
  3. 3

    Grant least-privilege permissions (principle of least privilege)

    Grant only what is needed. Avoid adding to db_owner for convenience. GRANT on objects or schemas is safer than broad roles.

    -- Permisos de lectura/escritura por rol
ALTER ROLE db_datareader ADD MEMBER [app_user];
ALTER ROLE db_datawriter ADD MEMBER [app_user];

-- O permisos granulares
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO [app_user];
GRANT EXECUTE ON OBJECT::dbo.usp_MiProc TO [app_user];
  4. 4

    Verify the granted permissions

    Query the user's effective permissions to confirm everything ended up the way you expected.

    SELECT dp.name AS principal, p.permission_name, p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE dp.name = 'app_user';

// common mistake

A user with no mapped login becomes 'orphaned' after restoring on another server. Fix it with ALTER USER [app_user] WITH LOGIN = [app_user]. Never use the sa account for applications and never grant db_owner by default: it is the number-one cause of privilege escalation.

// when it's worth an expert

If you handle many accounts, audits, or compliance (PCI, ISO), defining a clean permission model is delicate. At dba.mx we design least-privilege security schemes with auditing, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.