How do you reset the SA password?

How on earth do you reset the sa password? I know how to go into the dialogs and reset a password. That's now what I'm asking about. It runs a little deeper than just click, click, new password, done!

I have no idea what the SA password is. Nor does the previous user of this machine. The previous user says he never had SQL Express ever running on this machine.

This journey started when I tried to create a new database and was told I didn't have permissions to do so. Okay, I decided to just give myself the appropriate permissions. Nope, I can't give myself nor anyone else permissions.

I tried changing the password using SSMS. I get a message saying I don't have permissions to change it.

I tried using the following SQL script. Again, no per开发者_开发技巧missions.

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO

The database is SQL Server 2008 Express (10.0.2531.0). SQL Server Management Studio is SSMS 2008. OS is Windows 7 Enterprise I'm a local admin, and a domain user. I created a local admin account for logging into SSMS Machine is on a domain. I have no problems connecting to our network database servers.

Any suggestions? This could be a simple fix. Thanks...

This should help: start SQL Server in single-user mode. This will allow local administrators to connect as a sysadmin fixed server role. A detailed description of how to do this can be found here.

people also can try to change password this way by the below SP

EXEC sp_password NULL, 'yourpassword', 'sa'

hope may help other. thanks

You could use: Reset-DbaAdmin Powershell cmdlet from https://dbatools.io.

This function allows administrators to regain access to local or remote SQL Servers by either resetting the sa password, adding sysadmin role to existing login, or adding a new login (SQL or Windows) and granting it sysadmin privileges.

This is accomplished by stopping the SQL services or SQL Clustered Resource Group, then restarting SQL via the command-line using the /mReset-DbaAdmin paramter which starts the server in Single-User mode, and only allows this script to connect.

Using Reset-DbaAdmin will restart your SQL Server.

Reset-DbaAdmin -SqlServer sqlcluster

The simplest method I've found so far is to run SQL Server Management Studio / SQL Express under the SYSTEM context with Sysinternals PSEXEC app. After installing (copying psexec.exe to your computer, running it and accepting the EULA), you can type the following to invoke a system-context instance:

psexec -s -i <path to ssms.exe/sqlservr.exe>

You can use the GUI and don't require single-user mode to effect changes. I had problems with an unknown client tying up the snigle-user connection and this saved me.