70-432: Execute as Owner

Another topic you’ll need to understand in order to pass the 70-432 is how you can grant permissions to a user to accomplish a certain goal, without granting too much access.  Today, I want to cover how you can use the EXECUTE AS OWNER to grant access to a table without granting direct access to that table.

Sound confusing?

It’s not as difficult as it sounds.  Let’s cover a quick example, then you’ll see how easy it can really be.

Scenario 1

You have the following table:

 1: CREATE TABLE test (
 2:       pk INT
 3:     , co1 VARCHAR(10)
 4: )

And you fill in some data:

 1: INSERT INTO test
 2: select 1,'a' UNION
 3: select 2,'b'

You have a database user, “student”.  This user cannot be given direct table access to test, but needs to be able to read rows from test.  So we build the following stored procedure.

 1: CREATE PROCEDURE usp_readTest
 2: AS
 3:     SELECT pk, co1
 4:     FROM test
 5:

You then grant EXECUTE permissions on this procedure to “student”.

 1: GRANT EXECUTE ON usp_readTest TO student

 

When “student” tries to run the execute statement he gets the error message:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘test’, database ‘AdventureWorks’, schema ‘dbo’.

What do you try first?

Whenever I have built a procedure that will grant the user a permission that they haven’t explicitly been granted, The bit I usually miss is the WITH EXECUTE clause.  In this case if I saw the error above, I’d alter the procedure with the following command.

 1: ALTER PROCEDURE usp_readTest
 2: WITH EXECUTE AS OWNER
 3: AS
 4:     SELECT pk, co1
 5:     FROM test

Then, when the user calls it again, it would run as my user (since my login is the owner of this object), rather than as student.

 

Summary

I’ll be honest, if you build the original stored procedure, it should have returned the rows without any errors (at least it did on my 2008 R2 instance.

When you work with permissions, learning when you want to use the EXECUTE AS clause will become just as easy as explicitly granting the permissions you’re looking for.  By using it along with groups, you can come up with a really good control on who has access to what.

If you have any questions, please send them in.  I’m here to help!

2 Comments on "70-432: Execute as Owner"


  1. HI,
    why doesn’t ownershiph chaining allow to execute the stored procedure?
    Assuming that the stored procedure was created by the administrator , its owner is dbo so if “student” can execute the stored procedure he can also delete anything in the database. Am I wrong?

    thanks

    Mic

    Reply

    1. Absolutely! If the administrator creates a stored procedure, and in that stored procedure you can delete data in a table, and that stored procedure is created with the EXECUTE AS OWNER option, and the user is given permission to execute… then he or she would be able to delete rows. That’s how you can open up specific functionality to a user without giving them too much access.

      For example, I set up web applications with their own account. Those accounts are given access to a custom group that has read permission on all the web tables, and write access to all the transactional tables. But when they need to remove records, they have to go through a stored procedure that only allows them to delete from a small set of tables. Actually most of those queries DELETE and OUTPUT INTO an archive. That way even if the account were compromised, we limit the amount of damage that account can do.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *