Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
I'd also be careful to use the schema prefix when creating, altering and calling the stored procedure. –  Aaron Bertrand Feb 14 '13 at 22:02
    
Completely missing the "Execute as owner" I was. –  tlum Feb 15 '13 at 3:23
    
For what it's worth, I just used OWNER in my example. That can be a few possibilities. –  Thomas Stringer Feb 15 '13 at 3:58
    
@AaronBertrand Good point, I've edited/fixed. A bad habit I need to correct :-) –  Thomas Stringer Feb 18 '13 at 13:58

I think you are looking for the EXECUTE AS clause. Making use of this you can have a stored procedure execute under the context of a user that does have the permissions to create the table.

http://msdn.microsoft.com/en-us/library/ms188354.aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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