Yes, it should be working how you think. Below is an example proving this:
-- create the dummy login
create login TestLogin1
with password = 'p@$$w0rd';
go
use TestDB;
go
-- create the test user
create user TestUser1
for login TestLogin1;
go
-- create the proc to create a table
create procedure dbo.CreateTableProc
as
create table dbo.SomeTestTable(id int);
go
-- give TestUser1 perms to execute CreateTableProc
grant execute
on dbo.CreateTableProc
to TestUser1;
go
-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go
exec dbo.CreateTableProc;
go
revert;
go
-- unsuccessful, permission denied
Msg 262, Level 14, State 1, Procedure CreateTableProc, Line 4
CREATE TABLE permission denied in database 'TestDB'.
-- alter the proc to execute under my security context (with CREATE TABLE perms)
alter procedure dbo.CreateTableProc
with execute as owner
as
create table dbo.SomeTestTable(id int);
go
-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go
exec dbo.CreateTableProc;
go
revert;
go
-- successful, table created
Command(s) completed successfully.
In this example, TestUser1 does not have the permissions to create the table. We see that when the original version of CreateTableProc is called, as the CREATE TABLE DDL executes under the security context of TestUser1. But then by modifying the CreateTableProc stored procedure definition and including the WITH EXECUTE AS OWNER clause, now TestUser1 can successfully call the proc and create a table because now the CREATE TABLE DDL executes under the security context of the owner (my database user, in the db_owner role).
