The SQL Server Security Model: Logins, Users, Schemas, Roles, Least Privilege and Row-Level Security

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0.5035.5, Cumulative Update 10) at compatibility level 170; the connection test was done with .NET 10 and Microsoft.Data.SqlClient 7.1.1.

Guess First: What Can the Application User Do?

To "avoid dealing with permission problems", a team put the database user of the Bookstore API (Application Programming Interface) into the db_owner role. The application only reads and writes orders; what harm could it do? We impersonate that user and try it inside a transaction:

CREATE USER lazy_app WITHOUT LOGIN;
ALTER ROLE db_owner ADD MEMBER lazy_app;
GO
EXECUTE AS USER = 'lazy_app';
BEGIN TRANSACTION;
DROP TABLE dbo.Shipments;
SELECT OBJECT_ID('dbo.Shipments') AS shipments_object_id;
ROLLBACK;
REVERT;
SELECT COUNT(*) AS shipments_after_rollback FROM dbo.Shipments;

shipments_object_id
-------------------
NULL

shipments_after_rollback
------------------------
954755

The table was dropped; only the rollback brought back the 954,755 shipment records. A SQL injection hole in the application or a leaked connection string gives an attacker exactly this permission. We covered the hole itself in the injection post of the OWASP (Open Worldwide Application Security Project) series; this post covers the layer that limits the damage once a hole is found: logins, users, schemas, roles, least privilege, Row-Level Security and connection security.

Logins, Users and Schemas

Identity in SQL Server has two layers. A login is at server level and answers "who is connecting": a user name and password with SQL authentication, an external identity with Windows or Microsoft Entra ID authentication. A user is at database level and answers "what can they do in this database". A login has to be mapped to a separate user in each database. A contained database user, on the other hand, has no login and authenticates directly in the database; it makes moving the database to another server easier, but it requires the server-level contained database authentication setting to be on. In Azure SQL Database, a contained user with Microsoft Entra ID is the recommended route.

In the lab we create two logins: one for the application's runtime and one for migrations. We don't write the passwords into the script; we pass them with sqlcmd variables at run time:

CREATE LOGIN sqls_17_api WITH PASSWORD = '$(ApiPassword)', CHECK_POLICY = ON;
CREATE LOGIN sqls_17_migrator WITH PASSWORD = '$(MigratorPassword)', CHECK_POLICY = ON;
GO
SELECT name, CONVERT(varchar(6), password_hash, 1) AS hash_prefix, DATALENGTH(password_hash) AS hash_bytes
FROM sys.sql_logins
WHERE name LIKE 'sqls[_]17[_]%';

name|hash_prefix|hash_bytes
----|-----------|----------
sqls_17_api|0x0300|70
sqls_17_migrator|0x0300|70

The first byte of the hash shows the algorithm version: 0x03. SQL Server 2025 stores SQL login passwords with PBKDF2 (Password-Based Key Derivation Function 2) from RFC (Request for Comments) 2898, which according to Microsoft runs 100,000 iterations over Secure Hash Algorithm 512 (SHA-512). In SQL Server 2022 and earlier the version is 0x02: a single SHA-512 hash of the salted password. The difference is the cost of brute-forcing once password hashes are stolen. On an upgraded server you can see which login is stored in which format by looking at the first byte with the same query.

The schema is the most useful unit for granting permissions. You authorize schemas, not individual tables: dbo for business tables, api for the procedures exposed to the application in post 14, and security for the security functions we'll create shortly. When a new table is added, its permissions come from the schema.

Least Privilege: Separating the Runtime and Migration Users

At run time the application needs to read and write; not to create or drop tables. Schema changes happen only during deployment, with a separate identity:

CREATE USER sqls_17_api FOR LOGIN sqls_17_api;
CREATE USER sqls_17_migrator FOR LOGIN sqls_17_migrator;
GO
CREATE ROLE app_runtime;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO app_runtime;
DENY SELECT ON dbo.Customers (Email) TO app_runtime;
ALTER ROLE app_runtime ADD MEMBER sqls_17_api;

ALTER ROLE db_ddladmin ADD MEMBER sqls_17_migrator;
ALTER ROLE db_datareader ADD MEMBER sqls_17_migrator;
ALTER ROLE db_datawriter ADD MEMBER sqls_17_migrator;
GO
EXECUTE AS LOGIN = 'sqls_17_api';
SELECT TOP (1) CustomerId, FirstName, City FROM dbo.Customers ORDER BY CustomerId;
SELECT TOP (1) * FROM dbo.Customers;
DROP TABLE dbo.Shipments;
REVERT;
GO
EXECUTE AS LOGIN = 'sqls_17_migrator';
ALTER TABLE dbo.Customers ADD Phone varchar(20) NULL;
SELECT TOP (1) CustomerId, Email FROM dbo.Customers ORDER BY CustomerId;
REVERT;

CustomerId|FirstName|City
----------|---------|----
1|Grace|Istanbul
Msg 230, Level 14, State 1, Server b8be943222fa, Line 3
The SELECT permission was denied on the column 'Email' of the object 'Customers', database 'sqls_17', schema 'dbo'.
Msg 3701, Level 14, State 20, Server b8be943222fa, Line 4
Cannot drop the table 'Shipments', because it does not exist or you do not have permission.
CustomerId|Email
----------|-----
1|customer1@example.com

The runtime user can read the allowed columns, but SELECT * ran into the e-mail column and DROP TABLE was refused. The message of error 3701 is deliberately vague: "doesn't exist or you don't have permission". It doesn't tell an unauthorized user whether an object exists. The migration user can change the schema and read the data.

Permissions are managed with three commands. GRANT allows, DENY explicitly forbids, REVOKE removes a previously given GRANT or DENY. The rule: DENY always wins. Even if a user gets schema-level SELECT from one role, a column-level DENY from another role or on the user applies. The exception: members of db_owner and sysadmins aren't stopped by DENY in the database; that's the real danger of the opening example. And grant permissions to roles, not people: app_runtime, reporting, support. When a new service is added, only role membership changes.

If you use Entity Framework Core (EF Core), keep two connection strings: the application runs as sqls_17_api, and the migration bundle or script in the Continuous Integration/Continuous Delivery (CI/CD) pipeline runs as sqls_17_migrator. Calling Database.Migrate() at application startup breaks this separation, because it requires giving the runtime user DDL (Data Definition Language) permissions.

Customer Isolation with Row-Level Security

In the customer portal every customer must see only their own orders. You do that in the application by adding WHERE CustomerId = @currentCustomer to every query, or with a filter in the model as in the global query filter post of the EF Core series. A single forgotten query turns into an IDOR (Insecure Direct Object Reference). RLS (Row-Level Security) applies the same rule in the database, as a second line of defense:

CREATE SCHEMA security AUTHORIZATION dbo;
GO
CREATE FUNCTION security.fn_CustomerFilter (@CustomerId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS allowed
    WHERE @CustomerId = CAST(SESSION_CONTEXT(N'CustomerId') AS int)
       OR IS_ROLEMEMBER(N'reporting') = 1;
GO
CREATE SECURITY POLICY security.CustomerIsolation
    ADD FILTER PREDICATE security.fn_CustomerFilter(CustomerId) ON dbo.Orders,
    ADD BLOCK PREDICATE security.fn_CustomerFilter(CustomerId) ON dbo.Orders AFTER INSERT
    WITH (STATE = ON);

The predicate is an inline TVF (Table-Valued Function); SQL Server adds it automatically to every query that touches the table. The application writes which customer is connected into SESSION_CONTEXT right after opening the connection; for example the customer ID read from the authenticated user's JWT (JSON Web Token):

EXECUTE AS LOGIN = 'sqls_17_api';
SELECT COUNT(*) AS visible_orders FROM dbo.Orders;
EXEC sys.sp_set_session_context @key = N'CustomerId', @value = 2, @read_only = 1;
SELECT COUNT(*) AS visible_orders FROM dbo.Orders;
SELECT COUNT(*) AS customer_1_orders FROM dbo.Orders WHERE CustomerId = 1;
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
VALUES ('BK-X1', 1, 'Pending', SYSUTCDATETIME(), 0, 'TRY');
REVERT;

visible_orders
--------------
0
visible_orders
--------------
17359
customer_1_orders
-----------------
0
Msg 33504, Level 16, State 1, Server b8be943222fa, Line 6
The attempted operation failed because the target object 'sqls_17.dbo.Orders' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.

With no context, zero rows: a misconfiguration returns an empty result instead of leaking data (fail closed). With context 2 only customer 2's 17,359 orders are visible, even when WHERE CustomerId = 1 is asked for explicitly. The block predicate prevents inserting an order on behalf of another customer. @read_only = 1 keeps the value from being changed later on the same connection. If you use connection pooling, make sure the context is written on every request, at the moment the connection is opened.

The shape of the predicate directly affects performance. The OR IS_ROLEMEMBER(...) we added for the reporting role turned the plan from a seek into a scan:

|--Index Scan(OBJECT:([sqls_17].[dbo].[Orders].[IX_Orders_CustomerId]),  WHERE:([sqls_17].[dbo].[Orders].[CustomerId]=CONVERT(int,session_context(N'CustomerId'),0) OR is_rolemember(N'reporting')=(1)))
Table 'Orders'. Scan count 1, logical reads 1748, ...

|--Index Seek(OBJECT:([sqls_17].[dbo].[Orders].[IX_Orders_CustomerId]), SEEK:([sqls_17].[dbo].[Orders].[CustomerId]=CONVERT(int,session_context(N'CustomerId'),0)) ORDERED FORWARD)
Table 'Orders'. Scan count 1, logical reads 35, ...

Without the OR, the same count takes 35 logical reads instead of 1,748. Rather than adding workloads that need wide access, such as reporting, to the predicate with OR, it's better to solve them with a separate user and a separate path (for example a reporting replica or a signed procedure). Because the predicate function is an inline TVF embedded in every query, the rules from post 15 apply as they are.

Dynamic Data Masking Is Not a Security Boundary

DDM (Dynamic Data Masking) shows a masked version of a column to unauthorized users. So that the support team doesn't see customer e-mails:

ALTER TABLE dbo.Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
CREATE USER support_agent WITHOUT LOGIN;
GRANT SELECT ON dbo.Customers TO support_agent;
GO
EXECUTE AS USER = 'support_agent';
SELECT CustomerId, FirstName, Email FROM dbo.Customers WHERE CustomerId = 42;
SELECT CustomerId, Email FROM dbo.Customers WHERE Email = 'customer42@example.com';
SELECT COUNT(*) AS starts_with_customer4 FROM dbo.Customers WHERE Email LIKE 'customer4%';
REVERT;

CustomerId|FirstName|Email
----------|---------|-----
42|Linus|cXXX@XXXX.com
CustomerId|Email
----------|-----
42|cXXX@XXXX.com
starts_with_customer4
---------------------
11111

The mask is on the screen, but WHERE works on the real value. The support user confirmed which customer an e-mail address belongs to and can guess character by character with LIKE. DDM is a convenience that prevents accidental display in application screens; it's not protection against someone who can write queries directly. Real confidentiality needs column permissions (DENY), RLS or encryption.

Encryption: TDE, Always Encrypted and the Connection

Three different threats, three different tools. TDE (Transparent Data Encryption) encrypts the data and log files and backups on disk; a stolen disk or backup file can't be read, but anyone who connects to the database sees the data in plain text. Always Encrypted encrypts specific columns on the client; the key lives in the application, and SQL Server, even a sysadmin, never sees the plain value. The price is query capability: equality lookups work with deterministic encryption, but range queries and LIKE need a secure enclave.

The third threat is the network. The connection must be encrypted with TLS (Transport Layer Security), and the client must validate the server's certificate. We connected to the lab container with three different settings:

string[] variants =
[
    "Encrypt=Mandatory;TrustServerCertificate=True",
    "Encrypt=Mandatory;TrustServerCertificate=False",
    "Encrypt=Strict;TrustServerCertificate=True",
];

Encrypt=Mandatory;TrustServerCertificate=True: connected, encrypt_option=TRUE, protocol=TSQL
Encrypt=Mandatory;TrustServerCertificate=False: SqlException -2146893019: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
    inner: AuthenticationException: Certificate failed chain validation. Error(s): 'The certificate was not trusted., [Status: UntrustedRoot]
Encrypt=Strict;TrustServerCertificate=True: SqlException 0: The client was unable to establish a connection because of an error during connection initialization process before login. ... (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed)

The first line is the most dangerous: the connection is encrypted (encrypt_option=TRUE), but because TrustServerCertificate=True doesn't validate the certificate at all, someone in the middle can present their own certificate and the traffic flows through them. The second line is the correct behavior: the container uses a certificate it signed itself, the client doesn't trust it and refuses the connection. In production the fix is to put a certificate from a trusted CA (Certificate Authority) on the server, not TrustServerCertificate. The third line is the strict mode of Tabular Data Stream (TDS) 8.0: TLS is set up before any TDS messages, and according to Microsoft's documentation TrustServerCertificate is ignored in this mode. Because the certificate couldn't be validated, the handshake didn't complete; strict removes the "forget to check the certificate" option. The TrustServerCertificate=True in this post and in the series' other examples is only for the local container; it has no place in a production connection string.

What's Different in SQL Server 2019/2022

The login/user separation, the role model, RLS (since 2016) and DDM work the same in 2019 and 2022. The differences: storing SQL login passwords with PBKDF2 is specific to 2025; in 2019 and 2022 the hash version is 0x02, single-iteration SHA-512. TDS 8.0 and Encrypt=Strict came with SQL Server 2022; you can't connect to 2019 in strict mode, you use Mandatory with a validated certificate. Secure enclave support for Always Encrypted came with 2019 on Windows. SQL Server 2022 also brought ledger tables: an audit option that proves cryptographically that changes weren't tampered with afterwards.

Watch Out in Production

Dump and review permissions regularly. A query over sys.database_role_members and sys.database_permissions that lists who has which role and permission lets you see the difference with every release. Membership in db_owner should be an alert.

Don't keep secrets in code. The password in the connection string comes from a secret store (Azure Key Vault, a Kubernetes secret, an environment variable); if possible, use passwordless identity, that is a Microsoft Entra ID managed identity. The rules from the password storage and secret management post of the Web Security series apply here too.

Test RLS. For every table, write tests for "zero rows with no context", "another customer's rows aren't visible" and "can't write on behalf of another customer". RLS works only on the tables the predicate was added to; forgetting to add a new table to the policy is a silent hole.

Add auditing. SQL Server Audit records failed logins, permission changes and access to sensitive tables. The Extended Events in post 18 are for diagnostics; auditing is for compliance and incident investigation.

Common Pitfalls

Running the application as sa or db_owner. The opening of this post; it starts as a "convenience" in development and moves into production.

Connecting everyone with the same user. If the application, the reporting tool and background jobs share the same SQL user, you can neither narrow permissions nor tell who is who during blocking except by program_name.

Filling SESSION_CONTEXT with a value from the client. RLS is only as secure as the value written into the context. The customer ID must come from the authenticated token, not from the query string, and must be locked with @read_only = 1.

Mistaking DDM for encryption. A mask is no protection against someone who can write queries; the LIKE experiment above shows it.

A Small Experiment

★☆☆ Set up a role that gives sqls_17_api only EXECUTE on the api schema and remove the GRANT on dbo (REVOKE). Do the procedures from post 14 still work?

★★☆ Add customer isolation to the OrderItems table as well. The table has no CustomerId; write the predicate function with a join to Orders and examine how it shows up in the plan.

★★★ In an ASP.NET Core application, write an EF Core DbConnectionInterceptor that writes the customer ID from the JWT with sp_set_session_context after the connection opens for each request. Test that a connection from the pool doesn't keep the previous request's context.

The security model decides who can access what. But one day someone will come again and say "the database is slow". In the next post we build a diagnostic method that approaches that complaint by measuring: wait statistics, DMVs (Dynamic Management Views) and Extended Events.