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.