Testing query permission with EXECUTE AS command

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

It is embarrassing to announce that you have created a super duper database object for your manager and then found out that they don't have permission to run it.

There is a good and easy way to check if your intended user have enough permission to call the newly created object you crafted for them. On the other hand, of course you can use this way to make sure those who should not be able to access is really cannot access to the object.

Simply, run the EXECUTE AS command before the query like following:

Then try to query a view or running a stored procedure, it will or not return result based on the database user permission.

Once you are finished with your testing, you can use REVERT command to get back to last credential before the EXECUTE AS command