SQL Server logins vs users: give someone read-only access the right way

#The goal

An analyst needs to read data from one database. They shouldn't be able to change anything or see other databases. To set this up, you need to understand a difference that confuses many people: logins and users aren't the same thing.

#The two levels

Person → Login (server) → User (database) → Roles and permissions

A login with no user in a database generally can't use it. Each database has its own users, so one login can map to several.

#Step 1: create the login

Prefer Windows or Entra authentication where you can, because there's no password to manage.

USE master;

-- A Windows group is easiest to maintain
CREATE LOGIN [CONTOSO\sales-analysts] FROM WINDOWS;

If it has to be a SQL login:

CREATE LOGIN app_reporting
WITH PASSWORD = 'Use-A-Long-Generated-Secret-1!',
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON,
     DEFAULT_DATABASE = SalesDb;

Don't put real passwords in scripts you commit to Git. For an interactive user, you can add MUST_CHANGE after the password. It requires CHECK_EXPIRATION = ON.

#Step 2: create the user

Switch to the target database:

USE SalesDb;

CREATE USER [CONTOSO\sales-analysts] FOR LOGIN [CONTOSO\sales-analysts];

#Step 3: grant read-only access

You have three levels to choose from.

Option A: read everything in the database.

ALTER ROLE db_datareader ADD MEMBER [CONTOSO\sales-analysts];

Option B: read one schema. This is usually better.

GRANT SELECT ON SCHEMA::reporting TO [CONTOSO\sales-analysts];

Option C: a custom role, which is the easiest to manage when several groups need the same rights.

CREATE ROLE sales_readers;

GRANT SELECT ON SCHEMA::reporting TO sales_readers;
GRANT EXECUTE ON OBJECT::reporting.usp_MonthlySales TO sales_readers;

ALTER ROLE sales_readers ADD MEMBER [CONTOSO\sales-analysts];

Common fixed database roles:

Warning

Don't add people to sysadmin or db_owner to "just make it work". sysadmin bypasses all permission checks on the whole server, including every other database. Find the permission that's actually missing.

#Step 4: test it as that user

You don't need the person's password. Impersonate them, and then switch back:

USE SalesDb;

EXECUTE AS USER = 'CONTOSO\sales-analysts';

SELECT USER_NAME() AS whoami;

-- Should work
SELECT TOP (5) * FROM reporting.MonthlySales;

-- Should fail with error 229
DELETE FROM reporting.MonthlySales WHERE 1 = 0;

REVERT;

Always run REVERT, or you'll stay in that security context. See what a principal can do:

EXECUTE AS USER = 'CONTOSO\sales-analysts';
SELECT * FROM fn_my_permissions('reporting.MonthlySales', 'OBJECT');
REVERT;

EXECUTE AS USER limits you to the database. To test a login's server access, use EXECUTE AS LOGIN.

#Step 5: see who has what

Members of each database role:

SELECT r.name AS role_name, m.name AS member_name, m.type_desc
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_id
ORDER BY r.name, m.name;

Direct permissions in the database:

SELECT pr.name AS principal,
       pe.permission_name,
       pe.state_desc,
       pe.class_desc,
       CASE WHEN pe.class = 1 THEN OBJECT_SCHEMA_NAME(pe.major_id) END AS object_schema,
       CASE WHEN pe.class = 1 THEN OBJECT_NAME(pe.major_id) END AS object_name,
       CASE WHEN pe.class = 3 THEN SCHEMA_NAME(pe.major_id) END AS granted_schema
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name NOT IN ('public', 'guest')
ORDER BY pr.name;

Who holds high server privileges:

SELECT r.name AS server_role, m.name AS member
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
ORDER BY r.name;

#Common errors

One rule to remember: DENY beats GRANT. If someone's in a role that has SELECT but another role denies it, access fails. Look for state_desc = 'DENY' in the query above.

#Remove access

Disable a login first, if you're not sure of the impact. It's easy to reverse:

ALTER LOGIN app_reporting DISABLE;

Then, once you've confirmed nothing depends on it:

USE SalesDb;
ALTER ROLE sales_readers DROP MEMBER [CONTOSO\sales-analysts];
DROP USER [CONTOSO\sales-analysts];

USE master;
DROP LOGIN [CONTOSO\sales-analysts];

Caution

Dropping a login doesn't drop its database users. Those become orphaned. Before you drop anything, check if any jobs or applications use the login, and whether it owns SQL Agent jobs or databases.

#On Azure SQL Database

There's no USE statement to switch between databases. Connect to the right database directly. Prefer contained users with Entra ID, where the identity lives in the database and no server login is needed:

-- Connected to SalesDb as an Entra admin
CREATE USER [sales-analysts] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sales-analysts];

#Prevent it next time

• Grant to groups and roles, never to one person at a time.
• Apply the narrowest scope: schema or object rather than the whole database.
• Review sysadmin and db_owner membership every quarter.
• Keep permissions in scripts, so that you can repeat them after a restore.
• For the whole picture, see the security baseline.