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.
