locked
ALTER LOGIN for SA fails with 15151 error SQL Server 2005 RRS feed

  • Question

  • I need to change the password for the SA account on a SQL Server 2005 instance using TSQL.  I can change the password using Management Studio if I select the "Map Credential" option but this needs to be done via an automated process so I have to use TSQL.  SMO isn't viable and I've not tried it since I have instances ranging from SQL Server 7.0 to SQL Server 2012.  The exact error text is:

    Msg 15151, Level 15, State 1, Line 1

    Cannot alter login 'SA', because it does not exist or you do not have permission.

    I've tried several different syntactical variations of the ALTER LOGIN command but without success.  It is supporting a commercial application so I can't really afford to experiment with altering server settings.

    Can someone offer the correct way to change the SA password via TSQL?


    • Edited by Ira Davis Wednesday, April 10, 2013 3:02 PM
    Wednesday, April 10, 2013 2:57 PM

Answers

  • After some additional exploration, it looks like I can use SMO.  None of the instances I need to automate the password change on is lower than SQL Server 2000 and the ChangePassword method of the Login object works on versions 2000 and above.  Using SMO simplified the code in that I no longer need to connect use a SQLCommand and submit TSQL.  For anyone else needing to do this here is brief Powershell function to accomplish the task.

    Function reset-sapwd([string] $instance, [string] $password)
    {
        try
        {
            $target = New-Object Microsoft.SqlServer.Management.Smo.Server($instance)
            $target.Logins["sa"].ChangePassword($password)
        }
        catch
        {
            # perform any error handling or notification
        }
    }

    • Marked as answer by Ira Davis Wednesday, April 10, 2013 5:00 PM
    Wednesday, April 10, 2013 5:00 PM

All replies

  •  I can change the password using Management Studio if I select the "Map Credential" option

    Hello Ira,

    The SA = "System Administrator" account is a speciall account and there are limitations on modifying this account; you can map it to anything else.

    So, please only Change the Password and nothing else for this account.


    Olaf Helper

    Blog Xing

    Wednesday, April 10, 2013 3:02 PM
  • Thank you for this meaningless, irrelevant response.
    • Edited by Ira Davis Wednesday, April 10, 2013 4:09 PM
    Wednesday, April 10, 2013 4:08 PM
  • After some additional exploration, it looks like I can use SMO.  None of the instances I need to automate the password change on is lower than SQL Server 2000 and the ChangePassword method of the Login object works on versions 2000 and above.  Using SMO simplified the code in that I no longer need to connect use a SQLCommand and submit TSQL.  For anyone else needing to do this here is brief Powershell function to accomplish the task.

    Function reset-sapwd([string] $instance, [string] $password)
    {
        try
        {
            $target = New-Object Microsoft.SqlServer.Management.Smo.Server($instance)
            $target.Logins["sa"].ChangePassword($password)
        }
        catch
        {
            # perform any error handling or notification
        }
    }

    • Marked as answer by Ira Davis Wednesday, April 10, 2013 5:00 PM
    Wednesday, April 10, 2013 5:00 PM
  • If you are looking for simpler SQL PowerShell commandlets, could you please file a feature request at https://connect.microsoft.com/SQLServer/Feedback ?

    Please consider upgrading your SQL 2000 instances 

    http://blogs.msdn.com/b/sqlagent/archive/2013/01/22/sql-server-2000-extended-support-for-sql-server-2000-ending-soon.aspx


    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, April 11, 2013 11:58 PM
  • If you are looking for simpler SQL PowerShell commandlets, could you please file a feature request at https://connect.microsoft.com/SQLServer/Feedback ?

    Please consider upgrading your SQL 2000 instances 

    http://blogs.msdn.com/b/sqlagent/archive/2013/01/22/sql-server-2000-extended-support-for-sql-server-2000-ending-soon.aspx


    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Two lines of SMO code is pretty simple.

    Upgrade to 2012?  Great concept but let me explain.  I'm one of a great many Microsoft customers.  I know this will come as a shock to those of you at Microsoft but we actually *use* your products to accomplish something called business objectives or work.  However unlike the software business with gross profits of 70%, we are a manufacturing company and operate on margins about 20 - 30 times smaller.  We have to make choices sometimes between multiple projects.  I don't mean between projects like "Hey we have this extra $400 billion in unused cash.  Let's send a monkey to Mars or get everyone new Surface Pros."  No, I mean between projects like upgrading an aging sales force automation with numerous problems that requires constant around the clock support and intervention to make it work, implementing CRM, or extending the data warehouse.  All of these projects are important but the business ultimately decides which one or two gets funded.  We have a number of apps that are using Windows 2003 and SQL Server 2000.  Should they be upgraded?  Yeah.  Let's present a case for upgrade to the business. 

    Business case (IT):  We now have about 98% of our Windows servers virtualized.  We have some old systems running on relatively new hardware so we're greatly reduced the likelihood of hardware failure.  But now we need to upgrade some out of support software.

    Business Response (Purse holder):  Does that mean the apps are not working or they're failing?

    IT: No, they're working fine now but the vendor can't provide code fixes for bugs we don't have.  We have lots of problems but that's because our developers think if code compiles its production-ready and if the app performs poorly, simply throw more resources at it.

    Business Response:  You do know we're having cut-backs and we've eliminated jobs to try to stay afloat in these difficult economic times?  And you want us to fund software upgrades so that an application will work pretty much exactly as it does now but we'll have "support" for problems we don't actually have? 

    Business Case:  Well, if you put it like that, these apps will probably run a while longer. 

    Friday, April 12, 2013 12:16 PM
  • Dear Ira,

    Where can I type in your function? Is it after I run Powershell.exe? I am a Powerbuilder programmer, trying to let user change their password. The original script sp_password 'xxx', 'yyy' worked for the past. It fails now with error code: 15151. Therefore, I am going to try your function + a change of Powerbuilder scripts.

    Regards,

    David

    Wednesday, November 11, 2015 8:51 PM