SQL Server Performance Forum – Threads Archive

SQL wierdness – SQL Agent Proxy Account Problem

hi there I think I have found a minor problem with SQL and other posts ive seen have had similar issues, We have another client who had a similar issue and Microsoft couldnt solve the problem and said "roll back SP4" Situation Both servers 2003 with SQL2000, the remote server B is SP3a, server A SP4 Server A runs a job which accesses a linked server B The job works fine when run as sa or an account with sa rights. But because the user needs to edit this job from time to time and add steps I want that user to manage the job (and only that job) Thus that user needs to be the job owner and I dont want them having sa rights (the user is a domain user so windows auth) I think this really sux that SQL doesnt have a role like Manage Jobs or something Anyway I use the proxy account in Job system tab so the job will be run as the same domain user as the SQL Agent service is using (ie the proxy account will run all jobs owned by non sys admin as the account you specify) Thing is it works sometimes and other times it wont, without making any changes ? This is the wierdest thing Ive ever seen in SQL Can any gurus out there explain that one ?, before you say roll back SP4 (ie do a SQL reinstall) Ive tested this out on SP3a for 2 other servers and no joy, couldnt even get it to work. Bizare !

sorry – the error i actually get when the job fails is Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000] (Error 7410). The step failed. cheers any help is helpfull

KBAs
http://support.microsoft.com/kb/823429
http://support.microsoft.com/default.aspx?scid=kb;en-us;811031 to resolve your issue. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>