Using “Execute As” to Test a Stored Procedure as a Different User

February 19th, 2009

In an earlier blog post, I wrote about how to use “Execute As” in a stored procedure to access a separate database. When you include “Execute As” in a stored procedure, you can pull data from a separate database without giving your users direct permissions on that database.

Another way I really like to use “Execute As” is to test the permissions on my stored procedures. You may have had a time or two where a stored procedure ran great when you ran it, but when your user tried it… not so great.

Here’s how to test as that user from either Query Analyzer or SQL Server Management Studio. First, run this line of code, substituting SOMEDOMAIN and Someuser with the appropriate information.

execute as login = ‘SOMEDOMAIN\Someuser’

Now run this line of code. It should return the login you just entered.

SELECT SUSER_NAME(), USER_NAME();

Now run your stored procedure.

When you are done testing, make sure you run this line (word, actually) of code:

Revert

This will set the login back to your login.

Posted in All, SQL Server | Trackback | del.icio.us | Top Of Page

Leave a Reply

You must be logged in to post a comment.