About dynamic SQL in SQL Server, it does not check for permissions when dynamic SQL is used

What is dynamic SQL?

I read in a book that when we use dynamic SQL, SQL checks for permissions. But when we do not use it, then there is no check of permissions. Why so?

I made a stored procedure, which updates 开发者_StackOverflow中文版a table.

create proc steve_SP AS update steveTABLE set eid = 2 where ename = 'henry'

OWNER of steve_SP & steveTABLE is STEVE.

I gave permission to a new user, 'Bob', to execute steve_SP, but not any permission on steveTABLE.

Now, when Bob executes the procedure, then it executes successfully.

But, when the code of the procedure is changed (dynamic SQL is used) to code below.

create proc steve_SP AS EXECUTE (update steveTABLE set eid = 2 where ename = 'henry' )

And then, Bob executes the procedure. This time the execution of steve_SP FAILS. Why so?

Procedures are a way for you to control access to a table.

You can deny a user all access paths to a table - except via DML stored procs - so it makes sense that CRUD within procs would work where a normal CRUD wouldn't work for the user.

However, when you run dynamic SQL, that is actually run in a separate SPID (process) thus it is as if the user is running the code in the dynamic SQL in a new Query Window.

Therefore, first scenario:

create proc steve_SP AS update steveTABLE set eid = 2 where ename = 'henry'

User executes steve_SP - which internally makes the update -> OK

Second:

create proc steve_SP AS
EXECUTE ('update steveTABLE set eid = 2 where ename = ''henry''')

The user has access to steve_SP, so that runs. In there is runs EXECUTE, which spins up and runs this in another process:

update steveTABLE set eid = 2 where ename = 'henry'

Update directly? -> no. You can use WITH EXECUTE AS options to get around this.