Test Security with Execute As

I learned a cool little trick the other day, something that will help me immensely with testing my SQL Server 2005 security setup. The trick I learned is the Execute As clause in T-SQL.

I have a database that I want certain users to be able to work with, but not others. In the past I would set up security as I thought it should be set up and then ask a user to test it out. This is a pain in the butt, to put it nicely. First of all, people are busy and don’t always want to be bothered to test things for a DBA. Second of all, people don’t like to know they can’t do things. With Execute As, I no longer have to bug any users. I can “Execute As” that user.

For example, let’s suppose I don’t want UserA to be able to Select from the ExecutiveSalary table in the Employee database. If I’m a member of the sysadmin role (which I am) I can simply say:

Execute as user=’UserA’

Select *

From Executive Salary

SQL Server will try to do the Select statement using UserA’s permissions rather than my own, and UserA never has to know. This is just one of those easy tricks that are sometimes easy to miss, but that really make an administrator’s life easier.