SQL Server logins, users and roles explained

SQL Server splits identity in two. A login lets you connect to the server; a user gives that login an identity inside one database. Permissions are granted to users (and to roles, which are groups of users) per database, and to logins for server-wide powers. sa is a login with every power there is. The right setup for an application is a login of its own, a user in its database only, membership of db_datareader and db_datawriter (or narrower grants on a schema), and EXECUTE if it calls stored procedures - nothing at the server level. This post explains each piece, gives the scripts, and covers the two problems that catch everyone eventually: orphaned users after a restore, and login failures with unhelpful messages.

Logins and users: two layers#

Every connection goes through two checks.

1. Authentication, at the server. The client presents a login name and password (SQL Server authentication) or a Windows identity. If the login exists, is enabled and the password matches, the connection is open. Logins live in master and are listed in sys.server_principals and sys.sql_logins.
2. Access, per database. When the connection uses a database - its default database, or one named in the connection string, or a USE statement - SQL Server looks for a user in that database mapped to the login. Users are listed in each database's sys.database_principals, and the mapping is by security identifier (SID), not by name.

A login with no user in a database cannot use that database, with two exceptions: members of the sysadmin server role, who enter every database as dbo, and databases where the guest user is enabled, which it is not in user databases by default and should stay that way.

This split is why a login can have different rights in different databases, and why moving a database to another server needs care: the users travel with the database, the logins do not.

The sa account#

sa is the built-in SQL login with SID 0x01, a permanent member of the sysadmin fixed server role. It can do anything: create and drop databases, change server configuration, create logins, read every table, and run operating system-level operations exposed to SQL Server. It cannot be removed from sysadmin and it cannot be dropped.

What that means for you:

• Keep `sa` for administration. Use it from SSMS or sqlcmd when you are managing the server, not in application connection strings. A leaked sa password is a leaked server.
• Give it a long random password, and store it in a password manager, not in a text file next to the code.
• Do not disable or rename it on a hosted server unless you know what depends on it. Both are possible (ALTER LOGIN sa DISABLE;, ALTER LOGIN sa WITH NAME = ...;) and both are standard hardening advice on servers you fully own. On a hosted server, first create another sysadmin login you have tested, or you can lock yourself out of your own instance.

On RE:NODE, a SQL Server plan comes with an sa password generated for that server, and a database created for you and set as sa's default, so SQL Server Management Studio opens straight into it. That is the starting point; the rest of this post is about not using sa for everything. Connect to SQL Server with SSMS covers the first sign-in.

Fixed server roles#

Server roles carry server-wide permissions. You add logins to them with ALTER SERVER ROLE ... ADD MEMBER.

SQL Server 2022 added a set of narrower fixed server roles whose names start with ##MS_, such as ##MS_ServerStateReader## (view server state for monitoring), ##MS_DefinitionReader## (view object definitions) and ##MS_DatabaseConnector## (connect to every database). They are useful for monitoring tools that would otherwise be given sysadmin just to read dynamic management views.

An application login should be in none of these.

Fixed database roles#

Database roles carry permissions inside one database. You add users to them with ALTER ROLE ... ADD MEMBER.

db_datareader and db_datawriter cover all current and future tables in the database, which is convenient and slightly broad. For finer control, grant on a schema instead: permissions granted on a schema apply to every object in it, including objects created later.

Note what is missing from db_datareader and db_datawriter: EXECUTE. An application that calls stored procedures needs that granted separately.

A least-privilege login for an application#

The script below creates a login for an application, gives it a user in its database, and grants what a typical web app needs at runtime. Run it as sa or another sysadmin.

USE [master];CREATE LOGIN [orders_app]    WITH PASSWORD = N'use-a-long-random-generated-password',         DEFAULT_DATABASE = [app],         CHECK_POLICY = ON;USE [app];CREATE USER [orders_app] FOR LOGIN [orders_app] WITH DEFAULT_SCHEMA = [dbo];ALTER ROLE [db_datareader] ADD MEMBER [orders_app];ALTER ROLE [db_datawriter] ADD MEMBER [orders_app];GRANT EXECUTE ON SCHEMA::[dbo] TO [orders_app];

Login and user can have different names; giving them the same one makes the mapping obvious when you read it a year later. DEFAULT_DATABASE means the login lands in the right database even if the connection string forgets to name one.

Migrations are the awkward part. Entity Framework Core, Flyway and similar tools create and alter tables, which the runtime login above cannot do - by design. The clean pattern is a second login used only by the deploy step:

USE [master];CREATE LOGIN [orders_migrate] WITH PASSWORD = N'another-long-password', DEFAULT_DATABASE = [app];USE [app];CREATE USER [orders_migrate] FOR LOGIN [orders_migrate];ALTER ROLE [db_ddladmin] ADD MEMBER [orders_migrate];ALTER ROLE [db_datareader] ADD MEMBER [orders_migrate];ALTER ROLE [db_datawriter] ADD MEMBER [orders_migrate];

If that is more ceremony than the project warrants, a single login in db_owner of its own database is still far better than sa: it can wreck one database, not the server. EF Core migrations in production covers how to run migrations as a separate step so the two-login pattern is practical.

Several applications on one server should each get their own database, login and user. Then a leaked connection string, a SQL injection bug or a bad migration in one app touches one database.

Schemas, GRANT, DENY and checking what a user can do#

Permissions can be granted at three levels: the database, a schema, or a single object. Schema-level grants are usually the right granularity:

CREATE SCHEMA [reporting] AUTHORIZATION [dbo];CREATE ROLE [reporting_reader];GRANT SELECT ON SCHEMA::[reporting] TO [reporting_reader];CREATE USER [bi_tool] FOR LOGIN [bi_tool];ALTER ROLE [reporting_reader] ADD MEMBER [bi_tool];

Creating your own role and granting to it, rather than granting to users directly, means a second reporting user is one ALTER ROLE away and the permissions are written down in one place.

DENY overrides GRANT from any other role, with one exception: it has no effect on sysadmin members or the database owner, who bypass permission checks. Use it sparingly - for example, to stop a reporting role reading a dbo.Payments table that its schema grant would otherwise include.

To see what a user can actually do, impersonate it:

EXECUTE AS USER = 'orders_app';SELECT * FROM fn_my_permissions(NULL, 'DATABASE');SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'DELETE') AS can_delete;REVERT;

To audit the other direction - who has access to what - list role memberships at both levels. Run these every so often, and especially after someone has been "temporarily" given more rights to fix a problem:

-- Server roles and their membersSELECT r.name AS server_role, m.name AS login_nameFROM sys.server_role_members AS rmJOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_idJOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_idORDER BY r.name, m.name;-- Database roles and their members, in the current databaseSELECT r.name AS database_role, m.name AS user_nameFROM sys.database_role_members AS rmJOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_idJOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_idORDER BY r.name, m.name;

Anything in sysadmin besides sa and the logins you deliberately created for administration, and anything in db_owner that is not a migration login, deserves a question.

When access needs to depend on the row rather than the table - a multi-tenant app where each customer must only see their own orders - roles are the wrong tool. Row-level security, available in every edition including Express, attaches a filter predicate function to a table so the database itself adds the tenant condition to every query. It is a useful second line of defence behind the application's own checks, not a replacement for them.

EXECUTE AS followed by the failing statement is also the fastest way to reproduce a permissions error an application reports, without touching the application.

Passwords, policies and changing credentials#

CHECK_POLICY = ON applies a password policy to the login. On Windows that is the Windows password policy; on Linux there is no Windows policy to inherit, so do not assume a weak password will be refused - generate a long random one and the question never arises. CHECK_EXPIRATION adds expiry, which suits human logins more than application logins - an application whose password expires at 3 a.m. is an outage, not security.

To rotate an application's password without downtime, the usual approach is two logins or a short maintenance window:

ALTER LOGIN [orders_app] WITH PASSWORD = N'the-new-long-password';

Existing connections stay open after a password change; only new ones need the new password. Update the environment variable that holds the connection string, restart the application so its pool reconnects, and confirm in sys.dm_exec_sessions that nothing is still connected with the old one after a while. To lock a login out immediately - for example after a leak - disable it and kill its sessions:

ALTER LOGIN [orders_app] DISABLE;SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'orders_app';-- KILL <session_id>; for each one

Orphaned users after a restore#

Users map to logins by SID. A SQL login created on a different server has a different, randomly generated SID, even with the same name. So when you restore a database from another server, its users arrive pointing at SIDs that do not exist here. These are orphaned users: the user is in the database, the login is on the server, and connecting fails because they do not match.

Find them:

SELECT dp.name AS orphaned_userFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sidWHERE dp.type = 'S'  AND dp.authentication_type_desc = 'INSTANCE'  AND sp.sid IS NULL;

Fix each one by remapping the user to the login on this server:

ALTER USER [orders_app] WITH LOGIN = [orders_app];

The old sp_change_users_login procedure does the same job and is deprecated; ALTER USER ... WITH LOGIN is the current way. To avoid the problem in the first place, create the login on the new server with the original SID - CREATE LOGIN [orders_app] WITH PASSWORD = N'...', SID = 0x...; using the SID from the old server's sys.sql_logins. Migrating a database to SQL Server hosting covers this as part of the full move, and SQL Server backup and restore covers the restore itself.

Contained databases are the other way round the problem. With CONTAINMENT = PARTIAL and the server option contained database authentication enabled, you can create users with passwords that live entirely inside the database, no login needed, so the database moves between servers with its authentication intact. They are a reasonable choice when databases move often; most single-server setups do not need them.

Reading login failures#

Error 18456, "Login failed for user", is deliberately vague to the client so it does not help an attacker. The server's error log records a state number with the real reason. You can read the log with EXEC sp_readerrorlog; as a sysadmin, or in SSMS under Management, SQL Server Logs.

States 38 and 40 are the ones that look like password problems and are not. 38 usually means the connection string names a database where the login has no user. 40 means the default database was dropped or renamed - which, on a server where sa's default is the database created for you, can stop sa from connecting until you connect with master as the initial database and reset it with ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [master];.

FAQ#

What is the difference between a login and a user in SQL Server?

A login is a server-level identity that lets you connect. A user is a database-level identity, mapped to a login, that gives you access to one database. One login can have a user in many databases, with different permissions in each.

Should my application connect as sa?

No. Create a login for the application with a user in its own database and only the roles it needs. sa can drop every database and change server configuration, and an application connection string is the credential most likely to leak.

Is db_owner safe for an application?

Safer than sa, because it is limited to one database, but broader than an application needs at runtime. It can drop tables and change permissions. Use db_datareader, db_datawriter and EXECUTE for the running app, and db_owner or db_ddladmin only for migrations.

Why can't my login connect after I restored the database?

The database's user is orphaned: it points at a login SID from the old server. Remap it with ALTER USER [name] WITH LOGIN = [name];, or recreate the login with the original SID.

Can I create a read-only user for reporting?

Yes. Create a login and user, and add the user to db_datareader, or grant SELECT on just the schema the reports need. It is good practice to point reporting tools at a dedicated read-only login so a misconfigured report cannot change data.