1

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?

  • The SQL Engine control which commands can be execute or not. If the user is in DB_datareader DML commands going to fail. Everything else id going to work the same the generated plan will be save un the cache and Engine will be controlling the locks. – Krismorte Jun 4 '17 at 14:20
  • I am expecting a single word answer i.e. either Relational engine or Storage engine In case of relational engine how it will verify the user has access or not. Will parser or optimizer check for that. – info.sqldba Jun 4 '17 at 16:11
1

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)

0

Where does this permission evaluated when a user connects and runs a DML query?

As you are using SQL Login here; SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods login will be verified.

Next it will check if you have a corresponding user to the database you want to run DML Query against.

Object level authorization has to be performed after name resolution. I have not seen any official documentation which outline these steps. I believe it is designed to work perfectly and support team might have the details.

If permission is checked during early stage of Relational engine will execution plan be created and stored in cache?

No. There are phases before creating/selecting and saving an execution plan in the cache.

You can easily test this with following steps.

  • Create a store procedure (SP)
  • Execute the SP
  • Confirm the plan is in cache for that SP
  • Clear cache for that SP
  • Create a new user without privilege to execute the SP
  • Try to execute the SP which will fail
  • Check to see if the plan is in the cache (It will not be there).

My answer is transaction manager in storage engine which controls the locks and permissions am i right?

Storage engine is not involved in authorization process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.