When is SQL Server permission check done?

Question

When a user is to execute a complex stored procedure, inside of which there are quite a few tables / views or even another stored procedure.

My question is:

Before SQL server generates an execution plan, does it check the user's permission?

If not, generating execution plan itself is an overhead cost wasted if the user does not have the EXEC permission on this stored procedure in the first place.

If yes, it seems to me there is nothing consumable to the SQL Server engine, i.e. there is no securable identifier (like the stored procedure itself, tables inside the stored procedure etc) that the SQL Server engine can use to check against the user permission. All these securable identifier will not be generated until at least parsing is done.

So from a lifecyle perspective of executing a stored procedure, when does SQL Server permission check kick in?

Answer 1

This is spelled out in some detail here:

https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine

But the basic answer is that stored procedures have cached and reused query plans, and permissions checks are not necessary when objects owned by the stored procedure owner are accessed from a stored procedure. If valid ownership chains exist between the procedure and the object to be accessed, then only EXECUTE permission is checked before the stored procedure is started. Other permissions checks are skipped.

Could you come up with a step list about how a query (or SP) is processed after it is submitted?

The main thing to remember here is that all the queries in a batch are parsed and compiled before any of them starts to execute. Permissions are checked during execution. And it's even possible to reference a table in a query in such a way that permissions on that table are never actually checked.

If you watch an XE session like this:

CREATE EVENT SESSION [compiles] ON SERVER 
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[client_app_name]=N'Microsoft SQL Server Management Studio - Query'))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS)

And do something like

use tempdb

create table t(id int)
create table w(id int)

create user fred without login

grant select on w to fred

Then:

dbcc freeproccache

go
execute as user='fred'

go
select * from w
select * from t 
go

select 1 
where 1 = case when 1=1 then 1 else (select count(*) from t) end

revert

You'll see that the query plans in the first batch are both compiled, then the first query runs, then the second fails. The third query succeeds without a permissions check.