Why don't I have permission to change this SQL Server Express 2005 password?

I have access to an installation of SQL 2005 Express. I probably installed it myself some time ago. But haven't used it in a while.

I tried to access this instance via an 'sa' password I typically use for such local installs.

However this password did not work.

I double checked that mixed mode authentication is setup and that the sa user exists.

I can access the instance via开发者_如何学JAVA windows authentication and query the heck out of the DB's (even master). No problem.

Then I tried to reset the sa password using this sql:

ALTER LOGIN sa WITH PASSWORD = 'MyNewPassword';

It promptly fails with this error:

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

So why does my local Windows user not have permission to do this?

Have you followed these steps?

Are you logged in as the same admin user who installed the service in the first place, or local admin? Being able to read is one level of permission. Being able to change data and especially server-level objects is another set of permissions. Just because you can read does not mean you're going to be able to write.

If using different admin logins fails, you could always hack your sa password with a recovery tool

I know that this is an older post, but I still found it while searching for an answer to my own question.

2 options here:

1. Single User Mode
2. PSExec using NT Authority\System

Option 1: (from blog post)

• Open SQL Server Configuration Manager on the machine that is hosting the database
• Click on SQL Server Services
• Click on desired SQL Server instance and right click go to properties
• On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.
• Now use SQLCmd to add a new user to the database that has Sysadmin privileges
  • This technet blog post is very useful for getting the needed SQLCmd commands and syntax
  • Open command prompt and change directory to the "BINN" folder
  • Usually located at C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
  • This location changes depending upon your configuration
  • Use the following command to invoke SQLCmd:
  • SQLCMD –S <Server_Name\Instance_Name>
  • In my case this was SQLCMD -S SPExternal\Sharepoint - my machine name \ database instance name
  • Now use the following command sequence to create a new sys admin login:
  • Press enter after each line

1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’

2> go

1>SP_ADDSRVROLEMEMBER '','SYSADMIN'`

2>go

Lastly, take the server out of single user mode by reversing the initial 4 steps outlined above. Connect using the new login.

Option 2: Use PSExec to login as NT Authority\System

• Download PSExec.exe and place into Windows\System 32
• Open the command prompt as the administrator
• Execute SSMS from the command prompt on the machine hosting the SQL database using the following command
  • PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
  • Note that the path to SSMS may be different depending on the configuration of your system
• There are a few other caveats listed in the original article linked above
  • UAC may need to be disabled (worked Ok for me still enabled)
  • Your account needs to be a local admin for this work
  • May or may not work remotely

Option 2 was originally referenced in this DBA SE answer - thanks to the person who found it.