Login vs User in SQL Server: Who Gets In, Who Can Do What

Login vs user comes down to two words: authentication and authorization. A login gets you into the server. A user decides what you can do inside one database. People mix them up because most scripts create both in one go.

Two Layers, Two Jobs

A login lives at the instance level. It proves who you are. It can be a Windows account, a Windows group or a SQL Server login with its own password. A user lives inside one database. It decides what you can do there, through permissions and roles.

The two are linked by a number called the SID. A user points to a login through that SID. A login can have a user in every database it needs. The names don’t have to match, but matching names are easier to read. The table sums up the login vs user split.

A Login With No User

The first script creates a database named LoginUserDemo with an invoices table, and a SQL login named DemoReportLogin. The password is a placeholder, so replace it before you run the script anywhere that matters. The last query counts the matches in both lists.

IF DB_ID(N'LoginUserDemo') IS NULL CREATE DATABASE LoginUserDemo;
GO
USE LoginUserDemo;
GO
DROP TABLE IF EXISTS dbo.Invoices;
CREATE TABLE dbo.Invoices (InvoiceID int NOT NULL PRIMARY KEY, Amount decimal(10,2) NOT NULL);
INSERT INTO dbo.Invoices (InvoiceID, Amount) VALUES (1, 120.00), (2, 75.50);
IF SUSER_ID(N'DemoReportLogin') IS NULL CREATE LOGIN DemoReportLogin WITH PASSWORD = N'ReplaceWithAStrongPassword1!';
SELECT (SELECT COUNT(*) FROM sys.server_principals WHERE name = N'DemoReportLogin') AS LoginsFound,
       (SELECT COUNT(*) FROM sys.database_principals WHERE name = N'DemoReportLogin') AS UsersFound;

The login exists on the server, and the database knows nothing about it. To see what that means, the next script pretends to be the login with EXECUTE AS LOGIN. That statement needs the IMPERSONATE permission, or sysadmin. The login passes authentication, so the first query works. Then it tries to enter the database.

USE master;
GO
EXECUTE AS LOGIN = N'DemoReportLogin';
SELECT SUSER_SNAME() AS ConnectedAs;
GO
USE LoginUserDemo;

Msg 916, Level 14, State 2, Line 1
The server principal "DemoReportLogin" is not able to access the database "LoginUserDemo" under the current security context.

That’s the login without a user. It got through the door and found no room it can enter. An application sees a similar refusal. It gets Msg 916 when it runs USE. It gets error 4060, Cannot open database requested by the login, when its connection string names the database.

Add the User and Grant a Permission

The error ended that batch, so the impersonation is still active. Always run REVERT to return to your own identity. This is also the reason to keep REVERT in its own batch after a test that can fail. The next script reverts, creates the user and grants read access. Then it tests a read and a write as the login.

REVERT;
GO
USE LoginUserDemo;
GO
CREATE USER DemoReportUser FOR LOGIN DemoReportLogin;
GRANT SELECT ON dbo.Invoices TO DemoReportUser;
GO
EXECUTE AS LOGIN = N'DemoReportLogin';
SELECT USER_NAME() AS DatabaseUser, COUNT(*) AS InvoicesRead FROM dbo.Invoices;
GO
INSERT INTO dbo.Invoices (InvoiceID, Amount) VALUES (3, 10.00);

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'Invoices', database 'LoginUserDemo', schema 'dbo'.

Now the login can read the table. It enters the database as the user DemoReportUser, and the read returns both invoices. The insert fails with message 229, because the user holds only SELECT. That’s authorization at work. The login proved who you are, and the user decided what you can do.

How They Map

One query shows the link. It joins the two principal lists on the SID.

REVERT;
GO
SELECT dp.name AS DatabaseUser, sp.name AS LoginName
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.name = N'DemoReportUser';

A user maps to at most one login. Try to give the same login a second user in this database, and SQL Server refuses.

CREATE USER SecondUser FOR LOGIN DemoReportLogin;

Msg 15063, Level 16, State 1, Line 1
The login already has an account with the user name 'DemoReportUser'.

The rule runs in one direction only. One login can have a user in each database it needs. So a single login can reach many databases, with different rights in each. Several logins can’t share one user, though. When a team needs the same rights, create a role. Grant the permissions to the role and add each person’s user to it. That’s easier to audit than a permission list per person.

Questions That Follow

A user can exist with no login at all. CREATE USER ... WITHOUT LOGIN makes one, and it’s used for impersonation and for application roles. Nobody can connect as that user, but code can execute as it.

The other trap is the orphaned user. A restore on another server brings the users along with the database, but the logins stay behind in master. The user’s SID matches no login, so the person can’t get in. Create the login on the new server, then remap the user with ALTER USER ... WITH LOGIN.

You could argue that the two are one thing, since a script creates both in one go. They are two objects with two jobs. The day a restore or a copy separates them, that difference is the whole problem.

What to Remember

In login vs user terms, the login is for authentication, and it lives in the instance. The user is for authorization, and it lives in the database. They link through the SID. When someone can connect but can’t see a database, look for a missing user first. When someone can see a database but not a table, look at the permissions.

Grant permissions to roles, and keep the number of direct grants small. Test with EXECUTE AS and put REVERT in its own batch. When you finish with the demo, run the cleanup script. It removes the login as well as the database, because a login belongs to the server and stays behind otherwise.

USE master;
GO
REVERT;
GO
IF DB_ID(N'LoginUserDemo') IS NOT NULL
BEGIN
    ALTER DATABASE LoginUserDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE LoginUserDemo;
END;
IF SUSER_ID(N'DemoReportLogin') IS NOT NULL DROP LOGIN DemoReportLogin;

A login is not a permission, it is the key that gets you to the door.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.