Sql-server – Database Roles Security: In SQL Server architecture where does permissions are evaluated in RE or SE or Access methods

ArchitectureroleSecuritysql serversql server 2014

I got a question if i create a login and database user for a database named TEST, and i have given permissions to user as db_datareader only.

1) Where does this permission evaluated when a user connects and runs a DML query?
2) If permission is checked during early stage of Relational engine will execution plan be created and stored in cache?
3) My answer is transaction manager in storage engine which controls the locks and permissions am i right?

Best Answer

When your user without DML permissions submit the query, it's compiled. So nither parser nor optimizer controls permissions. The compiled plan (CP) can be seen using smth like this:

select *
from sys.dm_exec_cached_plans
     cross apply sys.dm_exec_sql_text(plan_handle) t
     cross apply sys.dm_exec_query_plan(plan_handle) 
where t.text like '%your query text%'

But it's just a compiled plan, not executable plan. Make note of plan_handle and paste it into 

sys.dm_exec_cached_plan_dependent_objects()

It will show nothing, no exacutable plan is built. I have no idea what name has a piece of code responsible for controlling permissions but the exact moment when the permissions are checked is when we passing from copiled plan (CP) to execution plan (MXC)

Related Question