When we think of SQL Server security, we often imagine logins and roles, who can access what.
But under the hood, SQL Server’s security context (the identity under which code runs) shapes how queries compile and cache.
Here’s the key:
If the same query runs under 10 different users, SQL Server might create 10 different plans .
Each security context creates a new entry in the plan cache (SQL Server’s “memory of compiled plans”).
That means more compilations, more CPU, and less reuse, all invisible until your system feels sluggish.
1. The Hidden Link Between Security and Performance
SQL Server’s optimizer does more than analyze data; it also considers who is executing code.
Security design impacts:
Plan cache reuse (1 plan vs 10 plans)
Metadata visibility during compilation
Ownership and permission checks
Runtime recompilations
Hidden predicates (RLS)
Masking logic (DDM)
Poorly designed security silently consumes CPU, memory, and IO.
Our goal: stable context, fewer recompiles, and reusable plans.
2. Setup – Safe Demo Environment
We’ll use AdventureWorks2022 .
Let’s create a sandbox schema and table for repeatable demos.
USE AdventureWorks2022;
GO
-- Create schema if needed
IF SCHEMA_ID(N'SalesNew') IS NULL
EXEC('CREATE SCHEMA SalesNew AUTHORIZATION dbo;');
GO
-- Clean up previous demo
IF OBJECT_ID(N'SalesNew.Orders', N'U') IS NOT NULL
DROP TABLE SalesNew.Orders;
GO
-- Create a table and seed data
CREATE TABLE SalesNew.Orders
(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
Amount MONEY NOT NULL
);
GO
INSERT INTO SalesNew.Orders (Amount)
VALUES (100), (200), (300);
GO
3. Ownership Chaining, Let Permissions Flow
When a procedure and table share the same owner, SQL Server skips extra permission checks when calling one from the other.
This is called ownership chaining and it saves CPU cycles.
-- Drop if rerunning
IF OBJECT_ID(N'SalesNew.usp_GetOrders', N'P') IS NOT NULL
DROP PROCEDURE SalesNew.usp_GetOrders;
GO
CREATE PROCEDURE SalesNew.usp_GetOrders
AS
BEGIN
SELECT OrderID, Amount FROM SalesNew.Orders;
END;
GO
Let’s test it with a user who only has execute rights:
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'ReaderUser')
DROP USER ReaderUser;
GO
CREATE USER ReaderUser WITHOUT LOGIN;
GO
GRANT EXECUTE ON SalesNew.usp_GetOrders TO ReaderUser;
GO
-- Works
EXECUTE AS USER = 'ReaderUser';
EXEC SalesNew.usp_GetOrders;
REVERT;
-- Fails (no direct SELECT)
EXECUTE AS USER = 'ReaderUser';
BEGIN TRY
SELECT * FROM SalesNew.Orders;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMsg;
END CATCH;
REVERT;
Because both belong to schema SalesNew (owned by dbo), the ownership chain is intact.
4. Broken Chains – Different Owners, Extra Work
If schema ownership changes, SQL Server can’t trust the chain – and must check every object again.
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'OtherOwner')
DROP USER OtherOwner;
GO
CREATE USER OtherOwner WITHOUT LOGIN;
GO
ALTER AUTHORIZATION ON SCHEMA::SalesNew TO OtherOwner;
GO
EXECUTE AS USER = 'ReaderUser';
BEGIN TRY
EXEC SalesNew.usp_GetOrders; -- fails
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMsg;
END CATCH;
REVERT;
GO
-- Restore
ALTER AUTHORIZATION ON SCHEMA::SalesNew TO dbo;
GO
Keep related objects under the same owner for predictable permissions and faster runtime.
5. Execution Context – CALLER vs OWNER
A stored procedure runs under either:
If you use CALLER, SQL Server might generate one plan per user.
IF OBJECT_ID(N'SalesNew.usp_UpdateOrders', N'P') IS NOT NULL
DROP PROCEDURE SalesNew.usp_UpdateOrders;
GO
CREATE PROCEDURE SalesNew.usp_UpdateOrders
WITH EXECUTE AS CALLER
AS
BEGIN
UPDATE SalesNew.Orders SET Amount += 10;
END;
GO
Better
ALTER PROCEDURE SalesNew.usp_UpdateOrders
WITH EXECUTE AS OWNER
AS
BEGIN
UPDATE SalesNew.Orders SET Amount += 10;
END;
GO
6. Dynamic SQL – The Context Trap
Dynamic SQL runs under the caller’s identity by default, breaking chains and plans.
IF OBJECT_ID(N'SalesNew.usp_ShowOrders', N'P') IS NOT NULL
DROP PROCEDURE SalesNew.usp_ShowOrders;
GO
CREATE PROCEDURE SalesNew.usp_ShowOrders
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM SalesNew.Orders';
EXEC(@sql);
END;
GO
GRANT EXECUTE ON SalesNew.usp_ShowOrders TO ReaderUser;
GO
EXECUTE AS USER = 'ReaderUser';
BEGIN TRY
EXEC SalesNew.usp_ShowOrders; -- Fails
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMsg;
END CATCH;
REVERT;
Fix: run under owner context.
ALTER PROCEDURE SalesNew.usp_ShowOrders
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM SalesNew.Orders';
EXEC(@sql);
END;
GO
EXECUTE AS USER = 'ReaderUser';
EXEC SalesNew.usp_ShowOrders; -- Works
REVERT;
7. Certificate Signing – Elevated Rights, Safe Plans
Need a procedure to perform privileged actions (like UPDATE)?
Instead of granting everyone UPDATE, sign the module with a certificate.
USE AdventureWorks2022;
GO
-- Ensure Database Master Key exists
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong!DMK#Password2025';
END
GO
-- Try to add encryption by the Service Master Key (ignore error if already added)
BEGIN TRY
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
END TRY
BEGIN CATCH
-- No action needed if it already exists
END CATCH;
GO
-- Create certificate
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'CertElevate')
DROP CERTIFICATE CertElevate;
GO
CREATE CERTIFICATE CertElevate
AUTHORIZATION dbo
WITH SUBJECT = 'Elevate UPDATE permission for SalesNew.Orders';
GO
-- Create user from cert
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'CertUser')
DROP USER CertUser;
GO
CREATE USER CertUser FOR CERTIFICATE CertElevate;
GO
GRANT UPDATE ON SalesNew.Orders TO CertUser;
GO
-- Create and sign proc
IF OBJECT_ID(N'SalesNew.usp_ElevatedUpdate', N'P') IS NOT NULL
DROP PROCEDURE SalesNew.usp_ElevatedUpdate;
GO
CREATE PROCEDURE SalesNew.usp_ElevatedUpdate
AS
BEGIN
UPDATE SalesNew.Orders SET Amount += 25;
END;
GO
ADD SIGNATURE TO SalesNew.usp_ElevatedUpdate BY CERTIFICATE CertElevate;
GO
GRANT EXECUTE ON SalesNew.usp_ElevatedUpdate TO ReaderUser;
GO
EXECUTE AS USER = 'ReaderUser';
EXEC SalesNew.usp_ElevatedUpdate; -- Works
REVERT;
Result
The caller only has EXECUTE, not UPDATE. The certificate grants the needed right to the module, not the user.
8. Recompilations Caused by Context Switching
Each user or SET option combination can cause SQL Server to compile separate plans.
USE master;
GO
SELECT TOP (20)
DB_NAME(st.dbid) AS DBName,
OBJECT_NAME(st.objectid, st.dbid) AS ProcName,
qs.execution_count,
qs.plan_generation_num AS RecompileCountLike,
CAST(qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS BIGINT) AS AvgTimeUs,
MIN(st.text) AS SampleText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.plan_generation_num > 1
GROUP BY
st.dbid, st.objectid,
qs.execution_count, qs.plan_generation_num,
qs.total_elapsed_time
ORDER BY qs.plan_generation_num DESC, qs.execution_count DESC;
Or detect multiple plans per query caused by different SET options:
WITH plans AS (
SELECT
qs.query_hash,
(SELECT CAST(pa.value AS INT)
FROM sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE pa.attribute = 'set_options') AS SetOptions
FROM sys.dm_exec_query_stats qs
)
SELECT query_hash, COUNT(DISTINCT SetOptions) AS DistinctOptionSets
FROM plans
GROUP BY query_hash
HAVING COUNT(DISTINCT SetOptions) > 1;
Best practice
9. Why Dynamic SQL Hurts If Used Wrong
Dynamic SQL can:
Bad example
use AdventureWorks2022
GO
CREATE OR ALTER PROCEDURE SalesNew.usp_SearchOrders_Bad @TerritoryID INT
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) =
N'SELECT SalesOrderID, TerritoryID FROM Sales.SalesOrderHeader
WHERE TerritoryID = ' + CAST(@TerritoryID AS NVARCHAR(10));
EXEC(@sql);
END;
GO
This generates one plan per value.
Fixed version
use AdventureWorks2022
GO
CREATE OR ALTER PROCEDURE SalesNew.usp_SearchOrders_Good @TerritoryID INT
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) =
N'SELECT SalesOrderID, TerritoryID FROM Sales.SalesOrderHeader
WHERE TerritoryID = @TID';
EXEC sp_executesql @sql, N'@TID INT', @TID = @TerritoryID;
END;
GO
Now all calls share one cached plan, secure and efficient.
10. Row-Level Security (RLS)
RLS injects hidden predicates to enforce row visibility.
If the filtered column isn’t indexed, expect scans.
USE AdventureWorks2022;
GO
-- Cleanup
IF EXISTS (SELECT 1 FROM sys.security_policies WHERE name = N'RLS_SalesPolicy')
DROP SECURITY POLICY RLS_SalesPolicy;
IF OBJECT_ID(N'dbo.fn_FilterSalesByUser', N'IF') IS NOT NULL
DROP FUNCTION dbo.fn_FilterSalesByUser;
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name IN (N'SalesUser1', N'SalesUser2'))
BEGIN
DROP USER SalesUser1;
DROP USER SalesUser2;
END;
GO
CREATE USER SalesUser1 WITHOUT LOGIN;
CREATE USER SalesUser2 WITHOUT LOGIN;
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_SalesOrderHeader_SalesPersonID')
CREATE INDEX IX_SalesOrderHeader_SalesPersonID
ON Sales.SalesOrderHeader (SalesPersonID);
GO
CREATE OR ALTER FUNCTION dbo.fn_FilterSalesByUser(@SalesPersonID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS fn_FilterSalesByUserResult
WHERE @SalesPersonID =
CASE USER_NAME()
WHEN 'SalesUser1' THEN 279
WHEN 'SalesUser2' THEN 288
ELSE NULL
END;
GO
CREATE SECURITY POLICY RLS_SalesPolicy
ADD FILTER PREDICATE dbo.fn_FilterSalesByUser(SalesPersonID)
ON Sales.SalesOrderHeader
WITH (STATE = ON);
GO
EXECUTE AS USER = 'SalesUser1';
SELECT COUNT(*) FROM Sales.SalesOrderHeader;
REVERT; -- we get an error
/*
The user SalesUser1 has no SELECT permission on Sales.SalesOrderHeader.
RLS only filters rows — it doesn’t grant permissions.
So, we need to explicitly grant SELECT to the user (or a role it belongs to).
RLS will then filter which rows they can see.
*/
GRANT SELECT ON Sales.SalesOrderHeader TO SalesUser1;
GRANT SELECT ON Sales.SalesOrderHeader TO SalesUser2;
GO
EXECUTE AS USER = 'SalesUser1';
SELECT COUNT(*) AS OrdersVisible FROM Sales.SalesOrderHeader; --will work
REVERT;
GO
EXECUTE AS USER = 'SalesUser2';
SELECT COUNT(*) AS OrdersVisible FROM Sales.SalesOrderHeader;--will woek
REVERT;
GO
Always GRANT SELECT to users participating in RLS.
RLS limits rows, not rights.
11. Final Thoughts
Security and performance aren’t rivals, they’re partners.
Every ownership, permission, and execution choice changes how SQL Server trusts and optimizes your queries.
Get them right, and SQL Server rewards you with fewer recompiles, reused plans, and quieter CPUs.
Get them wrong, and you’ll spend hours chasing invisible slowdowns.
Engineer security hand-in-hand with performance, and SQL Server will deliver truly optimized plans.
Hope it helps!!!