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';
GO
REVERT
GO
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.
Any suggestions?
Thanks so much in advance for your consideration and time!
If I understood your requirement correctly, you want to execute the stored procedures as a login who does not exist on the server and does not have access to the database. I don't think this is possible.
One way to accomplish what you are seeking to do would be to create an active directory group, add your windows users to that group, and then create a login on the server for the group, and grant permissions to the group. So you will be creating a single login (DOMAIN\YourUserGroup) and granting permissions to that login.
Sometimes people use one more level of indirection by creating a database role that has specific permissions, and granting membership in that role to the login. That would be useful if you had multiple user groups who had various permutations of permissions.
I appreciate your time for the response. If that is how it has to be done, then we have no choice. I don't mind that option but I wanted to make sure I am doing it the right way.