SQL 2000 sa password

Posted on 2004-11-09
Last Modified: 2007-12-19
Dear All

Im planning to change my sa password, but there are alot of automated jobs related to sa account, once i changed it the jobs are unble to start, i have change the login for those jobs but still some jobs can not start..

Please I want to change my sa password and do the required changes for all this jobs.

Question by:ibmas4002
    LVL 6

    Expert Comment

    by:Duane Lawrence
    Create a new account but try to give it less than sa privelges.  Move your processes over to the new account one by one.  Make sure one works before moving the next.  This will take a long time, but on a live database it is better.  After all processes are off of the SA account, you can change it at will.

    I will apoligize first, but I just can't help myself from saying this.
    I have seen a SQL Server admin T-Shirt that makes a joke of doing this, it comes from the Microsoft SQL Server World Wide User Group (sponsored by Microsoft).  

    LVL 19

    Accepted Solution

    u can try this

    -- start Of Stement

    declare @Jobname varchar(255)
    declare @NewOwner varchar(50)

    set @NewOwner = 'Non_Zero'

    declare mCursor Cursor for
          select [name] from msdb..sysjobs
    open mCursor
    fetch Next from Mcursor into @JobName
    While @@fetch_status = 0
       exec msdb..sp_update_job @job_name = @JobName
              , @owner_login_name = @NewOwner
          fetch Next from Mcursor into @JobName

    -- End Of Stement

    Melih SARICA

    LVL 6

    Expert Comment

    Amen to DuaneLawrence.

    Never setup production jobs to use the sa password. sa is just too powerful and programmers can totally ruin the database all too easily. I should know, 'cause I'm one of 'em. Though I have never screwed up a procduction database messed up by a programmer, I've come real close, and I've had to help recover from such messes more times than I can recall.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now