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)')
);
GO
INSERT dbo.Subscriptions
     (
         UserID
       , SubscriptionName
       , SubscriptionValue
     )
VALUES
     (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)
GO

When I access this, I see this data:

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

GRANT SELECT ON dbo.Subscriptions TO SallyDev
EXECUTE AS USER = 'SallyDev'
SELECT top 10
  *
  FROM dbo.Subscriptions AS s
GO
REVERT

Now I see this:

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

Use this to make testing easier.

SQLNewBlogger

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.