Testing as another user–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is one of those things I do often and thought I should write a short blog on the topic. Often I want to check how another user can interact with some object. I could certainly open a new window or change my connection string, but that can be disruptive. Not to mention I sometimes get confused about which user is in which window in SSMS or ADS.

There’s a better way. I can use the EXECUTE AS USER statement.

Testing Access

Here’s a quick example. I create a new table with Dynamic Data Masking. I want to see if another user sees masked content. Here’s my table:

CREATE TABLE dbo.Subscriptions (
    UserID int,
    SubscriptionName VARCHAR(200),
    SubscriptionValue MONEY MASKED WITH (FUNCTION ='random(100,1000)')
INSERT dbo.Subscriptions
       , SubscriptionName
       , SubscriptionValue
     (1, 'My first sub', 50.99),
     (1, 'Time', 24.99),
     (1, 'ESPN Mag', 19.99),
     (1, 'Popular Mechanics', 19.99),
     (1, 'The Guardian', 24.99)

When I access this, I see this data:

2019-04-08 11_23_41-Window

What does SallyDev see? I could log in as this user, but this is easier:

GRANT SELECT ON dbo.Subscriptions TO SallyDev
SELECT top 10
  FROM dbo.Subscriptions AS s

Now I see this:

2019-04-08 11_24_49-Window

The EXECUTE AS USER allows me to simulate another user. The REVERT brings me back to my context.

Use this to make testing easier.


I was using this technique recently and realized the I hadn’t blogged about it. I spent about 5 minutes creating a scenario and 5 minutes putting this together. You could do this, show some knowledge, and explain how this helps your scenario.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.