snippetsqlMinor

Stored Procedure as Create Table proxy?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

storedproxycreateproceduretable

Problem

With SQL Server it was my understanding, I thought, that you could elevate a users permissions with specific constraints.

For example, you need a user to create a specific table object in a specific schema. Rather than giving them wholesale create table permission, you could wrap the create table in a stored procedure and give them access to only that. In this way they would be constrained to only creating the object as specified within the procedure and not have any direct create table permission on the database... at least I know it works this way with Select, Insert, Update, Delete. The Create Table statement in the Stored Procedure throws the Permission Denied error.

Am I mistaken or just doing something wrong?

Solution

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).

Code Snippets

-- 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

-- 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

Context

StackExchange Database Administrators Q#34776, answer score: 4

Revisions (0)

No revisions yet.