Tag: sql server

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.