I simply would like a very limited user, in its own database, to call an update procedure on our database to tell us that it has updated a table, which it has access to.
I have allowed the user login ("Doc") the "Impersonate any login" permission. Doc also has impersonate rights on the "App" login, which exists despite what the error messages seem to say.
And I inserted the following statement near the beginning of the proc
EXECUTE AS LOGIN = 'App';
"App" is in the db_owner of the database that I want to update. I was running into an issue with the
WITH EXECUTE AS 'App' permission on the proc I was using, because our canned audit procedure calls procedures in another database, and I had read that such a permission was confined to the current database.
But for some reason when I try the
EXECUTE AS LOGIN statement it tells me that this very much used login cannot be used.
Cannot execute as the server principal because the principal "App" does not exist, this type of principal cannot be impersonated, or you do not have permission.
So, if this is telling me right,
- logins aren't the type of principal that can be impersonated, or
- despite the check boxes on the UI, "Doc" does not have the right to impersonate the user "App", or
- The login that my application uses 700,00 times a day, "does not exist".
Or this is just broken.
I'd prefer to give "sudo" type rights to limited users, but if I have to, I'll start traversing through the tree giving every single right the "limited" user needs for 24-hour access to table modifications which are kind of like the updates I want him to make. Despite that all I want him to do is call a proc that I created, update a particular column in a table in the Application database and log the result if it goes sideways.
UPDATE: Base on what srutzky says below, I tried turning
Trustworthy on in a restricted database with a new user and a new login and the rights to impersonate, and it worked. I guess I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.