This morning \ afternoon found myself reviewing Microsoft SQL Server Job. One of the jobs was failing and recording an error.
'EXECUTE AS LOGIN' failed for the requested login 'LAB\sqluser'. The step failed.
Tried a few things that had worked in past:
- Setting trustworthy on
alter database [db_name] set TRUSTWORTHY on;
Goggled and read blogs; the most informative & interesting:
Tibor Karaszi – SQL Server Agent jobs and user contexts
Finally ran sp_helpuser on the database in question and discovered that the login was not registered as a user in the database; that is though a system login existed, there was no contextual database user.
Here is a quick query for investigating user/logging mapping:
use [db-name]; exec sp_helpuser [user-name];
Here is a sample script for changing to the specific database and creating a new user:
use [db-name]; create user [LAB\sqluser] from LOGIN [LAB\sqluser];
That half-way fixed the problem, the next problem:
Executed as user: LAB\sqluser. The EXECUTE permission was denied on the object 'usp_moveData', database 'hr', schema 'dbo'.[SQLSTATE 42000] (Error 229). The step failed.
The quickest and most elaborate way to fix the latter:
use [db-name]; grant execute on schema::dbo to [LAB\sqluser];