SQL Server Authentication vs Authorization: Logins, Users, Roles and Least Privilege

SQL Server security rests on two separate questions. Authentication answers “is the person connecting really who they claim to be?”. Authorization answers “once inside, what can this person do?”. The two are often confused, yet most of the security gaps we see in the field come from the second question: accounts that authenticate correctly but hold far more permissions than they need.

This article covers SQL Server’s authentication methods, the difference between logins and users, how permissions are granted, and how to apply least privilege in practice.

Authentication: Who Are You?

A server accepts either Windows Authentication only, or Windows and SQL Authentication together (Mixed Mode). Use Windows Authentication only where possible: password policy, account lockout and account deactivation are managed centrally. If Mixed Mode is required, enforce the password policy on SQL logins and disable or rename the sa account.

Logins vs Users

• A login is server-level: it represents the right to connect to SQL Server.
• A user is database-level: it’s the login’s counterpart in a specific database.

For a login to access a database, it has to be mapped to a user in that database. The mapping is done by SID, so when a database is restored on another server where a login with the same name was created with a different SID, the user is left “orphaned”, and the login can’t reach the database even though it can connect.

-- Users without a login (orphaned)
SELECT dp.name AS user_name, dp.type_desc, dp.sid
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE sp.sid IS NULL
  AND dp.type IN ('S', 'U', 'G')
  AND dp.authentication_type_desc = N'INSTANCE'
  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys');

-- Reconnect an orphaned SQL user to an existing login
ALTER USER AppUser WITH LOGIN = AppUser;

Authorization: What Can You Do?

Permissions are granted at two levels:

• Server level: fixed server roles such as sysadmin, securityadmin and serveradmin, and server permissions such as VIEW SERVER STATE and ALTER ANY LOGIN. SQL Server 2022 and later also have new, narrower fixed roles such as ##MS_ServerStateReader##.
• Database level: fixed roles such as db_owner, db_datareader and db_datawriter; roles you define yourself; and GRANT, DENY and REVOKE at schema or object level.

-- A role for the application with only the permissions it needs
CREATE ROLE AppRole;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::Sales TO AppRole;
GRANT EXECUTE ON SCHEMA::Sales TO AppRole;
ALTER ROLE AppRole ADD MEMBER AppUser;

Grant permissions to roles, not people, and to roles at schema level where possible. When a new table or procedure is added, the role’s permissions cover it automatically.

Who Has Which Permissions?

-- sysadmin members
SELECT sp.name, sp.type_desc, sp.is_disabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS sp ON sp.principal_id = rm.member_principal_id
WHERE rm.role_principal_id = SUSER_ID(N'sysadmin');

-- db_owner members in this database
SELECT dp.name, dp.type_desc
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS dp ON dp.principal_id = rm.member_principal_id
WHERE rm.role_principal_id = DATABASE_PRINCIPAL_ID(N'db_owner');

Regularly reviewing privileged accounts and documenting the permission matrix is the first step of our security hardening guide. Use SQL Server Audit to record who does what.

Checklist

• Windows Authentication only where possible; password policy enforced in Mixed Mode.
• sa is disabled or renamed.
• sysadmin members are few and chosen deliberately; AD groups rather than individuals.
• Application accounts aren’t sysadmin or db_owner; schema-level role permissions are used.
• Orphaned users are checked regularly.
• The permission matrix is documented and reviewed periodically.