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:

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
EXECUTE AS USER = 'SallyDev'
SELECT top 10
  *
  FROM dbo.Subscriptions AS s
GO
REVERT

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.

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.

About way0utwest

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.