April 25, 2008 at 10:27 am
When I try to execute a job, it fails with a following:
Unable to perform a SETUSER to the requested username 'MyDomain\SQLAgentAccount' because the username is invalid for database 'MyDB'. The step failed.
MyDomain\SQLAgentAccount is a domain account that has a corresponding SQL Login and is a member of dbo role in MyDB. I was logged in and tried to execute the job with my domain account, which is a member of all server roles.
When I tried to execute:
SETUSER 'MyDomain\SQLAgentAccount'
I got:
Msg 15157, Level 16, State 1, Line 1
Setuser failed because of one of the following reasons: the database principal 'MyDomain\SQLAgentAccount' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.
Any suggestions?
April 25, 2008 at 10:33 am
Duplicate http://www.sqlservercentral.com/Forums/Topic490818-359-1.aspx
Please don't cross-post.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2008 at 10:40 am
I didn't know that posting on different forums is not allowed -- just wasn't sure what forum to post to. It seems like a security/access rights issue, but it may be something different ...
April 25, 2008 at 10:45 am
You'll get a reply, don't worry. The trouble with posting it twice is that someone might spend time trying to help you, only to discover you'd already been helped, or that more background had been given. 🙂
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2008 at 10:46 am
Got it -- sorry 🙁
April 25, 2008 at 10:55 am
Bits from BOL:
SETUSER is included in Microsoft SQL Server 2005 for backward compatibility only. SETUSER may not be supported in a future release of SQL Server. We recommend that you use EXECUTE AS instead.
So MS doesn't recommend using SETUSER, but it's being used behind the scenes when I try to run a job ... :w00t:
The same BOL article explains why I can't impersonate the SQL Agent's domain account with SETUSER:
Only use SETUSER with SQL Server users. SETUSER is not supported with Windows users.
This info doesn't help me much with fixing the problem, so I'll keep digging ...
April 25, 2008 at 10:59 am
Use the other post! This one is the duplicate! Leave it alone... 😉
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply