July 8, 2003 at 5:44 pm
I'm hoping someone can help me out on some security settings on our sql server 2000. If I run a simple script such as
EXEC master.dbo.xp_sqlagent_proxy_account N'GET'
I should either get an empty recordset back or the data for the sqlAgent proxy account. But instead I get a big fat
Access Denied.
I've check the settings from the server against the settings I got on my win2k pro box and they all seem to match but perhaps I'm missing something... anyone got any suggestions? I also am sure that this is probably whe the db owner chaining is not working right... we just upgraded sql to SP3 so I'm not sure why things are still screwed up 🙁
thanks for your help in advance.
-Francisco
-Francisco
July 9, 2003 at 7:03 am
Does the login you used have permission to run this command? Can you post exact error message from QA?
July 9, 2003 at 8:43 am
the logins I have tried, my login (member of administrators / builtin), Administrator, SA, my sql login who is a member of sysadmin.
the exact error is Access Denied. 🙁 this error only occurs on my server. It does not occur on my sql server developer ed. on my win2kpro workstation :(. any ideas?
-Francisco
-Francisco
July 9, 2003 at 9:43 am
What service account do you use to start SQL Server and Agent services? Is it in local administrator group?
July 9, 2003 at 10:29 am
the account that starts this account belongs to the administrators group (on the domain) but that group is not in the local administrators group. I suppose this must be why it is giving me this error. I won't be able to reboot till tonight so I'll keep you posted. (crossing fingers)
-Francisco
-Francisco
July 10, 2003 at 10:26 am
Thank you Allan_Cui
your solution worked like a charm.... now I just gotta figure out how to make this proxy work right :). I no longer get an access denied, but I did set the account to a local user who's rights are no more than a regular user account... I gave it rights to a particular directory for executing the program I need.
The sproc did not return the result so I ran a simple master..xp_cmdshell 'dir d:\' to see if it's working and I received the following error:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604
Did I not set up it up right?
I ran the following to set up the user account in the proxy...
EXEC master.dbo.xp_sqlagent_proxy_account N'SET', N'LocalServerName', N'sqlCMD', N'MyPassword'
-Francisco
-Francisco
July 10, 2003 at 11:50 am
Go to SQL Serevr Agent Property --> Job system --> uncheck 'Only user with sysadmin .....' and try again.
July 11, 2003 at 12:24 pm
I owe you big time Allen_CUI!
that was the answer 🙂 I want to say thank you!!!! man I've been cracking my head and didn't see that in BOL at all....
anything you need let me know 🙂
-Francisco
-Francisco
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply