SQL Server – View Job Properties Fails for Job Owner

sql serversql-server-2012

Domain user MyDomain\UserA has a login with a server role of public and is a user of the msdb database with membership in the SQLAgentUserRole.

The user owns a job, Daily Job. When logged in as the user, if I try to view the job properties, I get this error:

Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft Server, Error: 15517)

If I right click on Jobs and Select New Job... I get the exact same error.

I have even given the user membership in SQLAgentOperatorRole but get the same error.

In all my searching online, the only similar issue I've found is this question on MSDN, which is unanswered.

Any ideas? I'm at a loss here.

Best Answer

I had the same exact problem - I had a new user which wanted to add jobs. I added this user to msdb db and added him to sqlAgentOperatorRole and db_datareader, db_datawriter. Still, when trying to add a new job, the user was getting the same error:

Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft Server, Error: 15517)

I traced it down and it looks like when trying to add a new job (or doing many other things...) the following query is run:

exec master.dbo.sp_Msdbuserpriv 'serv'

So, obviously I had to add the new user to Master as well:

use master
create user [youNewUser] for login [yourNewUserLogin]
alter user [youNewUser] with default_schema=[dbo]

And now it works like charm...

Related Question