March 26, 2010 at 8:19 am
As the support DBA, I have sys admin rights on our SQL Servers, so I personally do not this issue. But, I have developers / support teams that need to be able to see SQL Agent Jobs on SQL 2000 machines (jobs, history, not start/stop). Now, these 2000 instances are already beyond SP3, so the MSDB role of "TargetServersRole" exists. I have granted the developers / support teams access to the SQL servers via Win Authenication / AD Groups. I have assigned them rights to MSDB, and membership in role of "TargetServersRole". However, when they use the SQL 2005 SQL Mgt Studio, and drill into SQL Agent and then jobs, they get the below error. I have additionally granted them 'select' permission to the agent tables, but did not work.
Are there any known issues with non-SA types reading SQL agent jobs of SQL 2000 via 2005 SSMS?
Solutions?
ERROR:
"Failed to retrieve data for this request Microsoft.Sqlserver.SmoEnum" message box. No other info provided by SSMS client.
March 26, 2010 at 8:24 am
i think you forgot to include the error
---------------------------------------------------------------------
March 26, 2010 at 8:28 am
Thanks. Added to root post.
March 31, 2010 at 9:41 am
Hi Martin,
Could you please share the workaround for this problem , iam also facing the same issue...
Thanku.
March 31, 2010 at 9:51 am
are they connecting remotely and what version are the client tools if they are.
does it work if they use enterprise manager rather than SSMS?
---------------------------------------------------------------------
March 31, 2010 at 9:58 am
Yes, they are connecting remotely. and Enterprise manager is working fine but when they use SSMS trowing error.
March 31, 2010 at 10:19 am
kiran-597331 (3/31/2010)
Yes, they are connecting remotely. and Enterprise manager is working fine but when they use SSMS trowing error.
then I highly recommend you use SQL2000 tools to administer SQL2000 instances, there are a number of things that don't work 100%.
The question was actually aimed at the owner of this thread.
---------------------------------------------------------------------
March 31, 2010 at 10:27 am
Hi George, Thanks for your prompt reply.
But when i regitered other sql 2000 instance, i'm able to see the jobs, i don't know where is the problem.
March 31, 2010 at 10:30 am
Try this post out:
http://www.sqlservercentral.com/Forums/Topic227694-5-1.aspx
It does not mention use of Windows logins. I use SQL Logins for my 2000 instances while connecting using SSMS 2005 and it seems to function enough for my purposes.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 31, 2010 at 10:34 am
George,
The answer is the same, Ent-Mgr works fine. Also, the SSMS sees Agent Jobs on a different 2000 machine, which is at a much later SP + CU build#. Since the 2000 in question is PROD, I am hesitant to update the CU's to later build, especailly since I have iSeries drivers working there as well (don't want to upset that apple-cart). Current build is 818, which is beyond SvcPk 3a.
March 31, 2010 at 12:19 pm
then personally this is a non-problem I would not waste time trying to fix. I would provide EM to administer this instance.
---------------------------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply