Creating SQL Server Logins and Database Users

2017-02-16 · mssql, security, tsql

One of the first things that confuses people coming to SQL Server from MySQL is the split between a login and a user. A login lives at the instance level (in master) and is what authenticates a connection. A user lives inside a single database and is what actually gets permissions on tables, schemas and procedures. A login without a user cannot touch any data; a user without a login is an "orphan" and cannot connect. You need both, mapped to each other.

I get asked about this every time a developer's restored database refuses to let them in, so here are my notes: creating both kinds of principals with T-SQL on SQL Server 2016, giving permissions through roles, and fixing orphaned users after a restore. You'll need sysadmin, or securityadmin plus db_owner on the target database, and SSMS or sqlcmd. Everything works on 2012 and later.

Check the authentication mode first

If you want SQL logins (username and password) rather than only Windows accounts, the instance must run in mixed mode. Check it first:

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsOnly;

1 means Windows authentication only. Change it in SSMS under Server Properties, Security, or with the registry key LoginMode set to 2, then restart the service. Without this you will get:

Login failed for user 'app_user'. Reason: An attempt to login using SQL authentication failed.
Server is configured for Windows authentication only. (Microsoft SQL Server, Error: 18456)

For an application account I create a SQL login with a strong password and turn off the Windows password-expiration policy, otherwise the app breaks in 42 days:

USE master;
CREATE LOGIN app_user
    WITH PASSWORD = 'Str0ng!Passw0rd#2017',
         CHECK_POLICY = ON,
         CHECK_EXPIRATION = OFF,
         DEFAULT_DATABASE = ShopDB;

For a domain account or group, no password is needed:

CREATE LOGIN [CORP\DBA-Team] FROM WINDOWS WITH DEFAULT_DATABASE = master;

I always grant to a Windows group rather than individual accounts. It saves a lot of ticket work when people change teams.

Then the database user

Switch to the target database and create the user. The user name does not have to match the login name, but keeping them identical avoids confusion:

USE ShopDB;
CREATE USER app_user FOR LOGIN app_user WITH DEFAULT_SCHEMA = dbo;

If the login does not exist you get error 15007, 'app_user' is not a valid login or you do not have permission, so create the login first.

Permissions go through roles

I don't grant rights directly to users anymore. Doing that once cost me an afternoon of untangling who could see what. Use the fixed database roles for simple cases:

ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;

For anything more specific, create a custom role once and add members to it:

CREATE ROLE app_exec;
GRANT EXECUTE ON SCHEMA::dbo TO app_exec;
GRANT SELECT ON SCHEMA::reporting TO app_exec;
ALTER ROLE app_exec ADD MEMBER app_user;

ALTER ROLE ... ADD MEMBER replaced sp_addrolemember in SQL Server 2012; the old procedure still works but is deprecated.

If the database is a contained database (CONTAINMENT = PARTIAL), you can skip the login entirely and authenticate at the database level:

CREATE USER report_user WITH PASSWORD = 'An0ther!Str0ngPass', DEFAULT_SCHEMA = dbo;

This requires sp_configure 'contained database authentication', 1 at the instance level. It is handy for databases that move between servers in an Availability Group.

Did it work?

Check the mapping between the server principal and database principal by SID:

SELECT dp.name AS db_user, sp.name AS login_name, dp.type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S','U','G') AND dp.principal_id > 4;

Then test the effective permissions as the new user without knowing its password:

EXECUTE AS USER = 'app_user';
SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');
REVERT;

Things that bit me

Orphaned users after a restore. When you restore a database on another instance, the users come with it but the logins do not, and the SIDs will not match even if you recreate a login with the same name. Find them:

SELECT dp.name FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type = 'S' AND sp.sid IS NULL AND dp.authentication_type = 1;

Fix each one with ALTER USER app_user WITH LOGIN = app_user; rather than the deprecated sp_change_users_login 'Auto_Fix'.

Error 4064, "Cannot open user default database. Login failed." The login's default database was dropped or is offline. Connect with sqlcmd -d master and run ALTER LOGIN app_user WITH DEFAULT_DATABASE = master.

Error 15023, "User, group, or role 'app_user' already exists in the current database." You are trying to CREATE USER for a login when an orphaned user with that name is already there. Use the ALTER USER ... WITH LOGIN fix above instead.

Password policy failures. With CHECK_POLICY = ON on a domain-joined server, a weak password fails with error 15118. Either meet the domain policy or, for test boxes only, set CHECK_POLICY = OFF.

Script your logins with their SIDs (CREATE LOGIN ... WITH PASSWORD = 0x... HASHED, SID = 0x...) and keep them next to your restore runbook. Then a restore on a standby never leaves you with orphans.