April 25, 2008 at 10:26 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 11:13 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 12:07 pm
I think I was able to solve the problem. This is how I think it works (correct me if I'm wrong):
When I start a job logged in as myself, the job is still executed in the SQL Agent's security context.
When I created the job steps, I set "Run As" to a SQL account having access to the linked server used in the steps. SQL Agent probably tried to SETUSER to that SQL account. I experimented with SETUSER in the Management Studio and found out that it works on the local server, but is not trusted on the linked one, even though the local SQL account is properly mapped to a SQL account on the linked server.
I modified the steps -- cleared up "Run As" box and preceded my SQL statements with " Execute As Login = N'MySQLLogin' " and finally was able to run my job, both manually and scheduled.
May 22, 2008 at 8:40 am
But what if the username being used, is for an exemployee (the previous SQL Admin), who was using their elevated AD credentials to run:
[Unable to perform a SETUSER to the requested username "DOMAIN\old.user.Admin" because the username is invalid for database....]
The function had been setup by the previous SQL Admin, using their elevated AD creds. They are now gone and the AD account has been disabled. I could enable the AD account again, but that defeats the purpose (security, among other things). How do I clear out the old SQL Admins username and change it to a networked service account (service.sql.run)?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply