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!