Execute as user

Hello!

Sorry, I haven't posted for a while.  Been doing so much other stuff.

Today, I was helping a user with granting permissions to her service account.  Even though I granted all her required permissions, her report still wasn't able to execute.

We kept looking on the EXECUTE permissions that she needed.  But to no avail.  

I then helped her out by running the proc using SSMS.  I needed to impersonate her service account in SSMS.  I first tried runas from the command prompt,

runas /noprofile /user:DOMAIN\SvcAccount "C:\Program Files\Microsoft SQL Server Management Studio 21\Release\Common7\IDE\SSMS.exe"

Enter the password for DOMAIN\SvcAccount

But, SSMS, for some reason just won't open.  Maybe, it is at a newer version, 21.  I really didn't try it at lower versions, too lazy LOL.

Anywho, I decided to use EXECUTE AS USER in my query,

USE [db_name]
GO
DECLARE @RC int
DECLARE @begin_dt datetime
DECLARE @end_dt datetime
-- TODO: Set parameter values here.
SET @begin_dt = '2025-06-01'
SET @end_dt = GETDATE()

EXECUTE AS USER = 'DOMAIN\SvcAccount'

EXECUTE @RC = [dbo].[sp_proc_name] 
   @begin_dt
  ,@end_dt
GO

We found out that she needed more permissions.  Without reading through hundred lines of code, we got an error that the service account needed read permissions to a separate database as it needed to join data from it.

She was very thankful for me helping her.  She said she wouldn't have known that she needed read on that database if it weren't for me.  It felt good to help someone today.

Hope this also helps someone with a similar issue.