We use impersonation for access to the database. We first have a generic user that has a login to the database (e.g., GenericUser). That login has CRUD permissions on the tables (GRANT SELECT ON dbo.TableName TO GenericUser).
We have users who access the database that don't explicitly have a login in the database.
We want to EXECUTE AS to capture who did the work such as this:
EXECUTE AS USER = 'DOMAIN\UserID';
We get an error like this:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "DOMAIN\UserId" does not exist, this type of principal cannot be impersonated, or you do not have permission.
How do I grant the generic login to have permissions to execute as the principal "DOMAIN\UserID"? I don't want to create actual users for each user to access the database.
I have done a Google search and am having a hard time finding the solution for this.
Thanks so much in advance for your consideration and time!