Post reply

Cannot Update SQL Server 2000 Agent jobs using SSMS 2008 R2

  • October 12, 2011 at 5:02 pm

    #258768

    We have a SQL Server 2000 production database server. I am using SSMS 2008 R2 client on my local machine to connect to it and i am able to.

    When i try to update a job step in one of the agent jobs on the 2000 server using SSMS 2008 R2, I am able to view the job, open the job, open the job step but for some reason all the fields appear to be locked (greyed out). The account that i am using is a restricted id on our production box. It has dmm_update and public database roles on the MSDB database and our application database.

    Surprisingly, i am able to do the update to the job step command via the SS2000 Enterprise Manager but i cannot do the same via SSMS 2008 R2.

    Is the account lacking any privileges?

    I am confused about why EM allows me to do that while SSMS does not?

    Please suggest.

  • October 13, 2011 at 1:54 am

    #1393849

    Is your SSMS patched to the latest service pack?

    I've seen some bugs in the RTM version regarding SQL Agent Jobs.

    -- Gianluca Sartori

  • October 13, 2011 at 2:41 am

    #1393867

    Did you try Gianluca's tip(Upgrading to the latest patch)?



    My blog

  • October 13, 2011 at 11:09 am

    #1394236

    Do you have any other SSMS installed I mean of 2005. Test with SSMS 2005 if you are able to update the jobs then we will know if this issue is particular to SSMS 2008 R2.

  • October 16, 2011 at 10:10 am

    #1395076

    This is two fold. On my machine, i just have the SSMS 2008 R2 installed but few of the others in my team have SS2000 EM and SSMS 2008 R2 installed on their local machine.

    I have installed the latest SS2008 R2 SP1 on all the local machines in my group.

    We still need to test the issue out for the reason.

    Coming back to your question, Does having SSMS 2005 installed on the same box as SSMS 2008 R2 surface any issues with the GUI?

  • October 17, 2011 at 12:53 pm

    #1395538

    Don't think that SSMS2005 & 2008R2 on the same box should matter. But have you tried the same thing with SSMS2005.

    Just want to see if it is an issue with only SSMS2008R2 or on your box.

  • October 18, 2011 at 4:07 am

    #1395763

    In general Microsoft support using SSMS on downlevel version only for those versions that it supports an upgrade from. Typically this is the -1 and -2 versions, which for SQL 2008 R2 are SQL 2008 and SQL 2005.

    You cannot upgrade from SQL 2000 to SQL 2008 R2 and you should not expect SQL 2008 R2 tools to work fully on SQL 2000. You should be OK using SQL 2008 or SQL 2005 SSMS with SQL 2000.

    BTW, with SQL 2012 SSMS you have very limited functionality when manageing SQL 2005 systems, due to the same reasons.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • October 18, 2011 at 3:23 pm

    #1396364

    I think you might be correct ...

    I did try to recreate the issue scenario today. Also, as per the previous suggestions, I did install the service pack 1 for my local sql server 2008 r2 instance.

    Unfortunately, the service pack installation does not seem to fix this except for the version update.

    Would you suggest installing sql server 2008 SSMS Only on my machine along side with the SQL Server 2008 R2 instance that is already running on the box so that i may be able to update the agent jobs on the SS 2000 server?

    On a second thought, If SQL Server 2008 R2 SSMS is limited to support only 2008 and 2005 instances then do i need to uninstall the 2008 R2 instance completely before i can install the 2008 management tools?

    I think yes.

    Can you please suggest if there is an workaround which does not require me to uninstall the 2008 R2 instance? I am afraid that would consume a lot of time for the uninstall and re-install.

    Please suggest.

  • October 18, 2011 at 7:59 pm

    #1396414

    empty string

  • October 20, 2011 at 4:36 am

    #1397200

    I have no problem editing SQL Server 2000 Jobs with SSMS 2008R2. Try using a sysadmin account and see if then you can edit the Job.



    My blog

  • December 1, 2011 at 5:43 pm

    #1415657

    Well, i already know that i could edit them using a sysadmin account but the type of accounts in question here are controlled accounts with restricted sets of privileges. They only have the necessary update privileges. When i use such an account from the Enterprise Manager, i can update the job step but when i attempt to do the same from SSMS (connected to the same instance), i am unable to do the update.

    I am really surprised that EM would allow me to do this but SSMS won't even though i am using the same account and connecting to the same sql server instance.

    Does SSMS interpret the permissions set in a different way? If so, Should the client application really have any sort of control over the permissions set of the account being used in addition to the database engine security.

    Any thoughts?

  • December 1, 2011 at 11:58 pm

    #1415709

    How are you connecting to SSMS? I have worked with the same configuration and I have never had problems. My guess would be that you are not connecting to the Server with the account you should connect, somehow the connection is being made with another account. Try running SSMS under the account you use for the agent jobs and see if then it will work.



    My blog

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply