Logins, users and roles in SQL Server: who can access what

The CrmDB database is restored from a backup on srv-sql-02, because srv-sql-01 is waiting for a disk replacement. The administrator creates the SQL login crm_app on the new instance with the password from the application’s configuration file, yet the application still cannot connect. The SQL Server error log shows a failed login, and an attempt to map the login to the database in SSMS ends with error 15023:

Login failed for user 'crm_app'. Reason: Failed to open the explicitly specified database 'CrmDB'. [CLIENT: 10.10.30.25]
User, group, or role 'crm_app' already exists in the current database. (Microsoft SQL Server, Error: 15023)

The database user and the server login have the same name, but SQL Server links them by a security identifier (SID), not by name. The new login received a new SID, so the user in the restored database was left without a login. This post sorts out the three levels of access, the login, the user and the role, so that moving a database does not require repairing accounts.

Login, user and SID

A login is an instance object. It is stored in the master database, appears in sys.server_principals, and decides whether SQL Server accepts a connection. A Windows login corresponds to an account or group in the directory and has no password in SQL Server, because Windows confirms the identity. A SQL login has a name and a password hash stored in master.

A user is a database object. It appears in sys.database_principals; it is the user that receives permissions and roles. It is stored in the database itself, so it goes into the backup and comes back with a restore. The user is linked to the login by a SID stored in both places. A Windows login has the SID of the Active Directory account, the same on every server in the domain. A SQL login receives its SID at CREATE LOGIN, so the same login created on two servers has two different identifiers.

-- Instance level: logins for Active Directory groups
CREATE LOGIN [CORP\GG-SQL-CRM-Read] FROM WINDOWS;
CREATE LOGIN [CORP\GG-SQL-CRM-Write] FROM WINDOWS;

-- Database level: users linked to the logins by SID
USE CrmDB;
CREATE USER [CORP\GG-SQL-CRM-Read] FOR LOGIN [CORP\GG-SQL-CRM-Read];
CREATE USER [CORP\GG-SQL-CRM-Write] FOR LOGIN [CORP\GG-SQL-CRM-Write];

A new login has only the CONNECT SQL permission and whatever the public role grants. It allows a connection to the instance, but only a user lets it into the CrmDB database.

Directory groups instead of personal accounts

Logins for individual people build up a list that nobody can verify: accounts of former employees and permissions granted “for a moment”. A better arrangement is logins for groups only, for example GG-SQL-CRM-Read for people who read CRM data and GG-SQL-CRM-Write for those who change it. Granting access means adding a person to a group in Active Directory, and disabling the account when an employee leaves also closes their access to SQL Server.

A group login cannot be disabled with ALTER LOGIN … DISABLE, which fails with error 15151. A group is blocked with a permission instead: DENY CONNECT SQL TO [CORP\GG-SQL-CRM-Read].

Server roles: sysadmin and its equivalents

The sysadmin role can do anything, and denials (DENY) do not apply to its members. Setup adds the sa account, the instance service identifiers (NT SERVICE\MSSQL$CRM, NT SERVICE\SQLAgent$CRM), NT SERVICE\SQLWriter and NT SERVICE\Winmgmt, plus the accounts named during installation. The service entries should not be removed. Apart from them, the role should contain one group: GG-SQL-Admins.

CREATE LOGIN [CORP\GG-SQL-Admins] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CORP\GG-SQL-Admins];

The securityadmin role manages logins, resets SQL login passwords and grants permissions at the server level and in the databases it can access. Microsoft documentation nevertheless says to treat it as equivalent to sysadmin, because the ability to grant access and configure permissions allows it to assign most server permissions. CONTROL SERVER should be treated the same way. Since SQL Server 2022 (16.x), fixed roles with the ##MS_ prefix offer a narrow scope: ##MS_LoginManager## creates and changes logins but cannot grant permissions, and ##MS_ServerStateReader## gives a monitoring account read access to DMVs (VIEW SERVER STATE).

Database roles and custom roles on a schema

Fixed database roles apply to the whole database. db_datareader reads all user tables and views, db_datawriter changes data in all tables, but neither allows running procedures. db_ddladmin can run any DDL statement, and according to the documentation its members can elevate their own permissions. db_owner can do anything in the database, including dropping it.

A more precise model is custom roles with permissions on a schema. The application’s objects live in the app schema and the role receives rights to the whole schema, so new tables and procedures are covered without further grants. When procedures and tables have the same owner, ownership chaining applies: a member of a role with only EXECUTE runs a procedure that changes tables it has no direct rights to.

USE CrmDB;

-- Read tables and views in the app schema
CREATE ROLE crm_read;
GRANT SELECT ON SCHEMA::app TO crm_read;

-- Read, change data and run procedures in the app schema
CREATE ROLE crm_write;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::app TO crm_write;

-- Role members are the users of directory groups
ALTER ROLE crm_read ADD MEMBER [CORP\GG-SQL-CRM-Read];
ALTER ROLE crm_write ADD MEMBER [CORP\GG-SQL-CRM-Write];

Role	What it grants	Who should get it
sysadmin (server)	everything on the instance, DENY has no effect	the GG-SQL-Admins group and the service accounts added by setup
securityadmin (server)	logins, passwords, granting permissions; equivalent to sysadmin	nobody outside sysadmin; from SQL Server 2022, ##MS_LoginManager## instead
##MS_ServerStateReader## (server, from 2022)	read access to DMVs (VIEW SERVER STATE)	a monitoring account
db_owner (database)	everything in the database, including DROP DATABASE	database administrators, not the application account
db_ddladmin (database)	any DDL statement	the account that deploys schema changes
db_datareader (database)	SELECT on all user tables and views	reports that read the whole database
db_datawriter (database)	INSERT, UPDATE, DELETE on all user tables	rarely; a custom role is usually better
custom role with GRANT … ON SCHEMA	selected rights in one schema, including EXECUTE	the application and user groups

The application account as a Windows login

An application with a SQL login keeps the password in its configuration file, usually in plain text and often in the repository too. Such a connection cannot use Kerberos, and changing the password requires a simultaneous change on every application server. It is better when the application runs under a Windows account: an IIS application pool or a Windows service running as a gMSA, for example CORP\svc-crm-app$, or as a regular service account. The connection string then contains no password, and Windows confirms the identity. Creating and installing the gMSA is covered in the fifth part of this guide.

{
  "ConnectionStrings": {
    "Crm": "Server=sql-crm.corp.example.com,14331;Database=CrmDB;Integrated Security=True"
  }
}

CREATE LOGIN [CORP\svc-crm-app$] FROM WINDOWS;

USE CrmDB;
CREATE USER [CORP\svc-crm-app$] FOR LOGIN [CORP\svc-crm-app$];
-- The application gets a custom role, not db_owner
ALTER ROLE crm_write ADD MEMBER [CORP\svc-crm-app$];

Mixed mode and the sa account

SQL Server runs in Windows Authentication mode or in mixed mode, which also accepts SQL logins. When all applications and people sign in with Windows accounts, mixed mode is not needed, and an attempt to sign in with a SQL login leaves the reason Server is configured for Windows authentication only in the error log. The mode is changed in SSMS in the server properties, on the Security page, and requires a service restart.

The sa account always exists and is a member of sysadmin. When installed in Windows mode it is disabled, it stays disabled after switching to mixed mode, and that is how it should remain. Renaming it is an extra: password-guessing attempts target the name sa, but the login keeps the SID 0x01, so it can still be found under its new name.

-- Signing in as sa stops working; open sessions and permissions remain
ALTER LOGIN sa DISABLE;
-- A name that cannot be guessed
ALTER LOGIN sa WITH NAME = [sqlbuiltin_x7q2];

Password policy for SQL logins

When a SQL login cannot be avoided, two options govern its password. CHECK_POLICY (default ON) applies the Windows password policy in force on the SQL server, which in a domain usually comes from the domain policy: length, complexity and lockout after failed attempts. CHECK_EXPIRATION (default OFF) adds password expiry. The combination CHECK_POLICY = OFF with CHECK_EXPIRATION = ON is not supported.

For an application login, a sensible set is CHECK_POLICY = ON and CHECK_EXPIRATION = OFF with a planned password change, because expiry means an application outage on the day the password lapses. A SQL login used by a person should have both options on and its first password set with MUST_CHANGE.

-- Complexity and lockout from the Windows policy, no password expiry
CREATE LOGIN crm_app WITH PASSWORD = '<strong-password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

SQL Server 2025 (17.x) stores SQL login password hashes with PBKDF2 (RFC 2898, 100,000 iterations of SHA-512), which significantly slows down brute-force guessing and makes sign-in slightly slower, mainly for applications without connection pooling.

A user without a login after a restore

In the situation at the start of this post, the restored database contains the user crm_app with the SID of the login from srv-sql-01, while the login on srv-sql-02 has a new SID. This is an orphaned user. ALTER USER … WITH LOGIN rewrites the user’s SID in the database to the SID of the specified login, and roles and permissions stay unchanged. The deprecated sp_change_users_login procedure does not handle Windows logins, and the documentation replaces it with ALTER USER.

USE CrmDB;
-- The user receives the SID of the crm_app login on this instance
ALTER USER crm_app WITH LOGIN = crm_app;

When a backup lands on a second server regularly, for example on the standby server from the sixth part of this guide, the SQL login is created there with the same SID as on the first server. Microsoft also provides the sp_help_revlogin script, which generates CREATE LOGIN statements with the original SID and password hash for every login on the instance.

-- On srv-sql-01: read the login SID
SELECT name, sid FROM sys.sql_logins WHERE name = N'crm_app';

-- On srv-sql-02: the same SID, so later backups match without a fix
CREATE LOGIN crm_app WITH PASSWORD = '<strong-password>', SID = 0x8E2B4C6A1D3F4E7B9A0C5D2E6F1A3B4C,
    CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

Windows logins do not have this problem: the SID comes from the directory, so once the [CORP\GG-SQL-CRM-Read] login exists on the new server, the user in the restored database matches straight away.

Domain Admins without sysadmin in SSMS

The Domain Admins group login has the sysadmin role, but an administrator signed in on the SQL server opens SSMS and gets a login error or cannot see the databases. The cause is User Account Control (UAC). A process started without elevation receives a filtered token in which privileged groups, such as Administrators and Domain Admins, are used for deny only. SQL Server does not match the group login against them and records error 18456 in the error log, usually with state 11.

From another machine the same user has the sysadmin role, because a network logon with a domain account is not filtered. Locally, running SSMS as administrator helps, but a better solution is a dedicated GG-SQL-Admins group with the sysadmin role instead of a Domain Admins login. Accounts in Domain Admins should not sign in to member servers at all, as described in the privileged accounts tiering model.

Check your own environment

Members of the sysadmin and securityadmin roles. A good result in sysadmin is a disabled sa (also under a new name), the NT SERVICE\… service identifiers and the GG-SQL-Admins group, with no rows in securityadmin. A personal account, BUILTIN\Administrators, Domain Admins or an application login in either role needs fixing.

SELECT r.name AS role_name, m.name AS member_name, m.type_desc, m.is_disabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
WHERE r.name IN (N'sysadmin', N'securityadmin')
ORDER BY r.name, m.name;

The authentication mode and the password policy of SQL logins. windows_auth_only equal to 1 means Windows mode. Every enabled login should have is_policy_checked = 1, and sa should have is_disabled = 1. An enabled login with is_policy_checked = 0 or is_locked = 1, or a login nobody can attribute to an application, needs investigating.

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

-- Internal logins with the ## prefix skipped
SELECT name, is_disabled, is_policy_checked, is_expiration_checked,
       LOGINPROPERTY(name, 'IsLocked') AS is_locked,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_last_set
FROM sys.sql_logins
WHERE name NOT LIKE N'##%'
ORDER BY is_disabled, is_policy_checked, name;

Users without a matching login, in every user database, excluding system users and users deliberately created without a login (authentication_type_desc = 'NONE'). An empty result is good. SQL_USER is an orphaned user to fix with ALTER USER … WITH LOGIN. WINDOWS_USER or WINDOWS_GROUP is an account without its own login: it may get in through a group login, but more often it is a leftover of a deleted account or a group without a login on the new server.

USE CrmDB;
SELECT dp.name AS user_name, dp.type_desc, dp.authentication_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 IN (N'INSTANCE', N'WINDOWS')
  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
ORDER BY dp.type_desc, dp.name;

Members of the administrators group as SQL Server sees them. The first call should return a row with privilege equal to admin; an empty result means the group has no login. The second returns the direct members of the group (nested groups are not expanded), and the list should match the people who are meant to administer the instances. An error means SQL Server cannot resolve the group name or cannot reach a domain controller; the procedure only handles global groups.

-- privilege = admin: the group has the sysadmin role
EXEC xp_logininfo 'CORP\GG-SQL-Admins';
-- Direct members of the group according to the domain controller
EXEC xp_logininfo 'CORP\GG-SQL-Admins', 'members';