Blog Post

Ownership Chains in 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 "SELECTClear"’” 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:

ownership_a

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating