Someone asked the question recently about allowing a user to run a process,but not execute the individual pieces of the process. I replied that ownership chains allow this, and then explained things. Here’s a short example of that.
Let’s suppose I have a process that removes data from a table and then reloads it. Perhaps it’s a report, perhaps it’s a static look at inventory, it doesn’t matter. Can I allow a normal user to execute the entire process, but not the clear or load pieces?
I can, and here’s how.
Suppose I have a clear procedure:
CREATE PROCEDURE dbo.spClear AS BEGIN select 'Clear' END go
For the sake of this example, I don’t have any work here, but the "SELECT ‘Clear"’” could be replaced by a TRUNCATE TABLE or other statement.
By default in SQL Server, I don’t have rights to execute procedures that I haven’t created. In this case, assume a db_owner created this procedure, so a normal user, Joe, would not be able to execute it. In fact, if I log in as Joe and run this, I get:
SETUSER 'Joe' go -- Fails EXEC spClear go
Msg 229, Level 14, State 5, Procedure spClear, Line 1
The EXECUTE permission was denied on the object ‘spClear’, database ‘db1’, schema ‘dbo’.
That’s expected, and it’s good.
Let’s also create a load procedure, as the db_owner.
CREATE PROCEDURE dbo.spLoad as BEGIN select 'Load' END go
Now to manage the process, let’s create a wrapper stored procedure that calls these. For the sake of tracking what’s happening, I have a few statements in there.
CREATE PROCEDURE dbo.spRefresh AS BEGIN select 'Refresh start' EXEC spClear EXEC spLoad SELECT 'Refresh End' END go
If I were to call this process, this is what I’d expect from the call stack:
- a call spRefresh
- a result set returning “Refresh Start’”
- a call to spClear
- a result set returning “Clear”
- return to spRefresh
- a call to spLoad
- a result set returning “Load”
- a return to spRefresh
- a result set returning “Refresh End”
- end of spRefresh
Obviously I could have actual delete or truncates in the clear procedure and then some insert in the load procedure. If I attempt to run this as Joe, I get:
Msg 229, Level 14, State 5, Procedure spRefresh, Line 1
The EXECUTE permission was denied on the object ‘spRefresh’, database ‘db1’, schema ‘dbo’.
Expected, because I haven’t granted Joe rights. Let’s do that and execute the procedure:
GRANT EXECUTE ON dbo.spRefresh TO Joe go SETUSER 'Joe' GO EXEC spRefresh
I get what I expected for results:
I have rights to execute spRefresh. Since the same owner exists for all three objects, dbo in this case, I have a chain that permissions are not checked for spLoad and spClear. I can execute them in the context of spRefresh.
What about separately?
EXEC spLoad
gets me:
Msg 229, Level 14, State 5, Procedure spLoad, Line 1
The EXECUTE permission was denied on the object ‘spLoad’, database ‘db1’, schema ‘dbo’.
and
EXEC spClear
gets me
Msg 229, Level 14, State 5, Procedure spClear, Line 1
The EXECUTE permission was denied on the object ‘spClear’, database ‘db1’, schema ‘dbo’.
A nice way to allow someone to execute a group of processes without allowing them to execute any individual one.
Voice of the DBA 