Before you let an LLM query SQL Server, decide exactly which data it can receive. Enforce that decision with database controls and a bounded application interface.
Give an LLM a Narrow Identity to Query SQL Server
A prompt asking for read-only behavior does not grant or remove database permissions. Generated SQL reaches the engine under an authenticated identity. That identity determines what the engine can authorize.
Use a dedicated nonadministrative identity for this workload. Do not share an application's write-enabled login or a database owner's connection. Separate identity also makes auditing and revocation easier to attribute.
I begin with the approved data contract rather than unrestricted table access. I also test denied operations using the actual identity. A harmless demonstration SELECT cannot establish that other operations are blocked.
The example assumes an isolated Windows SQL Server test instance and database. Provision the Windows account before creating its login. Replace the illustrative domain and account with the approved identity for your environment. CREATE SCHEMA must start its own batch, so a GO line sits before it.
-- Prerequisite: the Windows account YOURDOMAIN\SqlAssistant already exists.
-- Run the login statement with appropriate server administration rights.
CREATE LOGIN [YOURDOMAIN\SqlAssistant] FROM WINDOWS;
GO
-- Run the following in the isolated user database.
CREATE USER AssistantUser FOR LOGIN [YOURDOMAIN\SqlAssistant];
CREATE ROLE AssistantReaders AUTHORIZATION dbo;
ALTER ROLE AssistantReaders ADD MEMBER AssistantUser;
GO
CREATE SCHEMA AssistantData AUTHORIZATION dbo;
GO
CREATE TABLE dbo.AssistantOrder
(
OrderId int NOT NULL PRIMARY KEY,
TenantId int NOT NULL,
NetAmount decimal(12,2) NOT NULL,
InternalNote nvarchar(100) NULL
);
INSERT dbo.AssistantOrder VALUES
(1,1,125.00,N'Private review'),(2,2,225.00,N'Other tenant');
GO
CREATE VIEW AssistantData.OrderSummary
AS
SELECT OrderId, TenantId, NetAmount FROM dbo.AssistantOrder;
GO
GRANT SELECT ON OBJECT::AssistantData.OrderSummary TO AssistantReaders;
DENY INSERT, UPDATE, DELETE ON SCHEMA::AssistantData TO AssistantReaders;Expose Approved Columns Through Views
The view exposes the identifier, tenant, and amount while omitting the internal note. The reader role receives access to that specific view. It receives no direct grant on the base table.
A dedicated schema makes the approved interface easy to identify. Object-level grants keep newly created views outside access until reviewed. An automatic schema grant is another design choice with a broader future-object boundary.
Keep the schema under administrative control. The assistant identity should not create or alter its interfaces. A reader that can redefine a view can undermine the original column-selection contract.
Ownership chaining permits this approved view to read its base table under the ordinary same-owner arrangement. That is intentional interface behavior. Review every accessible procedure, function, and view for similarly exposed data paths.
Bind Row Access to a Trusted Identity
Column selection does not isolate tenants. Row-level security can enforce a filter beneath the approved view. The following demonstration binds the assistant user to tenant one and allows dbo to inspect the fixture. In my test it returned only the tenant one order, and CanReadBaseTable came back as 0.
This small mapping is intentionally fixed in the predicate. A production multi-tenant design needs an administrator-controlled identity mapping. Never accept an arbitrary model-selected tenant identifier as proof of tenant authorization.
CREATE SCHEMA AssistantSecurity AUTHORIZATION dbo;
GO
CREATE FUNCTION AssistantSecurity.TenantFilter(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS Allowed
WHERE (@TenantId = 1 AND USER_NAME() = N'AssistantUser')
OR USER_NAME() = N'dbo';
GO
CREATE SECURITY POLICY AssistantSecurity.TenantPolicy
ADD FILTER PREDICATE AssistantSecurity.TenantFilter(TenantId)
ON dbo.AssistantOrder
WITH (STATE = ON);
GO
EXECUTE AS USER = 'AssistantUser';
BEGIN TRY
SELECT OrderId, TenantId, NetAmount FROM AssistantData.OrderSummary;
SELECT HAS_PERMS_BY_NAME('dbo.AssistantOrder', 'OBJECT', 'SELECT')
AS CanReadBaseTable;
REVERT;
END TRY
BEGIN CATCH
REVERT;
THROW;
END CATCH;Do not rely on a writable session value as the sole authorization authority. An identity allowed to change that value can select another tenant. Validate any session-based design against its full trust and connection-pooling boundaries.
Privileged administrators can modify or disable a security policy. Row filtering therefore operates within the database's administrative trust model. Keep the assistant identity outside those privileged roles and permissions.
Bound Execution Before Accepting Generated SQL
Read-only queries can still consume substantial CPU, memory, storage reads, or tempdb space. Restrict the application interface to approved query patterns or reviewed parameterized templates. A keyword check for SELECT is not a complete SQL validator.
Use a real SQL parser if accepting limited generated query syntax. Validate object references, statement count, and permitted constructs. Parameterize values and reject attempts to redefine identifiers or concatenate executable fragments.
Set a finite command timeout and a total request deadline in the application. Limit concurrent requests and returned bytes as separate controls. A row limit alone does not bound the work needed to produce those rows.
Do not confuse the server's remote query timeout with incoming query limits. That setting concerns outgoing remote operations initiated by SQL Server. It does not impose a general execution deadline on the assistant's submitted SELECT.
Add Resource Governance Where Supported
Supported SQL Server editions and versions can apply Resource Governor policies to the dedicated workload. Classification should use a trusted authenticated identity rather than a spoofable application label. Review classification and limits with the server administrator.
Resource policies can limit workspace memory, parallelism, concurrency, and other supported resources. They are not automatically a hard wall-clock cancellation timer. CPU-time settings have their own enforcement behavior and need version-specific review.
The following diagnostic reads stored workload configuration without replacing an existing classifier. It is an administrative inspection query, not a grant to the assistant. Stored settings also need comparison with currently effective configuration.
SELECT name, request_max_memory_grant_percent,
request_max_cpu_time_sec, request_memory_grant_timeout_sec,
max_dop, group_max_requests
FROM sys.resource_governor_workload_groups
ORDER BY name;Query timeouts can leave cleanup work after cancellation. The application must close or reset connections appropriately before returning them to a pool. Test lock release and failure propagation instead of assuming a canceled request instantly disappears.
Use an isolated reporting copy when the workload's impact requires stronger separation. Approved replication and refresh rules then become part of the data contract. Report data freshness explicitly rather than quietly presenting stale results as current.
Audit Every LLM Query That SQL Server Runs
When you let an LLM query SQL Server, retain attributable execution evidence. Capture the authenticated identity, request identifier, time, approved query shape, parameters, and outcome. Apply appropriate protection to any logged sensitive values.
SQL Server Audit can record relevant schema-object access under an approved server and database audit configuration. Extended Events can supply duration and resource diagnostics. Choose the required events and retention rather than assuming every SELECT appears automatically.
Audit failures and denied attempts as well as successful reads. Compare database records with application request records through a trusted correlation identifier. An application-name string alone is editable and should not establish caller identity.
Review the returned data path beyond the database connection. Approved database access does not authorize forwarding private results to another service. The application needs an explicit disclosure boundary and controlled result handling.
Test Hostile LLM Query Requests Against SQL Server
Test attempts to read omitted columns, query another tenant, modify an updatable view, and access unrelated objects. Also test expensive joins and excessive result sizes. Each case should have an explicit expected rejection or bounded outcome.
Can the assistant obtain more data by asking a different question? Examine aggregate and inference risks within the approved interface. Small-group counts and repeated queries can reveal information without exposing the original omitted column directly.
I let an LLM query SQL Server only through a verified permission boundary. I review the interface again when its schema changes. A polite prompt is not a security badge with excellent handwriting.
The database-level parts ran on my SQL Server 2025 test database, while the login statement was only parse-checked. Validate actual login behavior, row filtering, cancellation, and audit capture in isolation. Keep the approved interface, identity mapping, and resource limits under accountable ownership.
Related reading on this blog: Implementing Row-Level Security (RLS) and AI Hallucinated a Table That Was Never There.
A read-only prompt is not a database boundary, it is an instruction backed by enforced permissions and execution controls.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
