Change the sa password in MS SQL. Change sa password in sql Ms sql express default sa login

FAQ 29.09.2022

FAQ

You are a respected and trusted database administrator in your organization. You maintain and update several important services that SQL Server runs in your production environment. You have taken the following steps (reflecting guidelines that any good DBA should follow) to protect your company's SQL Server databases from any form of unauthorized access.

Removed all built-in SQL Server administrative accounts.
Removed all users (except System Administrator or SA) from the SYSADMIN server role (all Windows Accounts and SQL Server accounts).
Set up an extremely complex password for the SA account that is hard to guess or remember.
Perform day-to-day maintenance of SQL Server under a domain user account that has Database Owner (DBO) permissions on user databases, but does not have SYSADMIN privileges on the system.
You did not write down the SA password anywhere so that others would not know it. After all, writing down passwords is not the best approach.

However, because you set up such a complex password for SA and used your domain account and not your SA account in your day-to-day work with SQL Server databases, the unthinkable happened. You have forgotten the SA account password of your SQL Server.

You are the only member of your group who knew the SA password. Now you can't remember what it was, and you need to do a few confirmations at the server level in the production SQL Server setup windows. What to do now? Here are some options.

Try logging in as SA with whatever passwords you can think of.
Look up the SA password on your computer's hard drive or in your email (you may have saved it in a file somewhere; it's a bad approach, but it might help).
Try restoring the Master database from a backup. This won't help in the long run, because if you don't remember the SA password, you'll experience the same problems.
Rebuild the Master database. This won't help much, as you will lose all system and server level settings and configurations, including accounts, permissions, and all server level objects.
Reinstall SQL Server 2012 and attach all user databases. This may not work as you will experience the same issues as when rebuilding the Master database.

Assume that all attempts to log in with the SA account have failed. It's time to call for reinforcements: the Microsoft Product Support Team. Here's what the staff has to offer.

SQL Server 2012 has a loophole that allows SYSADMIN access to production SQL Servers. However, your Windows account is required to be a member of the local Administrators group for Windows Server systems that run SQL Server 2012 services.

SQL Server allows any member of the Local Administrators group to connect to SQL Server with SYSADMIN privileges.

The following are the steps you need to take to take control of SQL Server 2012 as SA.

1.	Start an instance of SQL Server 2012 in single user mode from the command line, and you must be logged into the command prompt window as an administrator. You can also run SQL Server 2012 in minimal configuration, which will also run SQL Server in single user mode.
2.	At the command prompt (Run as Administrator), type: SQLServr.Exe -m (or SQLServr.exe -f) to start the SQL Server 2012 database engine. Do not close this command prompt window. It is possible that SQLServr.exe is located in the Binn folder specified in the PATH environment variable. If the SQL Server 2012 Binn folder is not in your PATH, you can always navigate to the SQL Server 2012 Binn folder using the directory structure of your computer. Typically, the Binn folder is located at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>.
3.	Once you start the SQL Server 2012 service in single user mode or minimal configuration, you can open another command prompt window as an administrator and run the SQLCMD command in it to connect to the SQL Server 2012 instance:

SQLCMD -S<Имя сервера\Имя экземпляра>For example: SQLCMD -S "SALEEMHAKANI" 1> CREATE LOGIN "<Имя_учетной_записи>" with PASSWORD="<Пароль>"2>GO 1>SP_ADDSRVROLEMEMBER"<Имя_учетной_записи>","SYSADMIN" 2>go

For example:

1> SP_ADDSRVROLEMEMBER SQL_SALEEM,"SYSADMIN" 2> GO

It is perfectly normal to forget the password from time to time, and this does not mean that even small inconveniences will arise. The recovery measures I've described will allow you to keep SQL Server up and running without any downtime or password decryption.

“sa” is the user login in MS SQL, which has the highest privileges by default, the account itself is local, often even turned off, but nevertheless often used. If you have lost or forgotten the password for this “sa” account, then you will not have access to manage your databases.

Default sa password

Let me remind you the default sa password, oddly enough sa

The only requirement is that you must be a local administrator everywhere

Change sa password in sql via GUI

Let's start, open start and go to All Programs > Microsoft SQL Server 2012 R2 > SQL Server Management Studio

Or you can open a command prompt and type ssms there.

This will open SQL Server Management Studio.

The default is Windows Authentication, which means that you can only log in with a local Windows account or a domain account, as long as you have permissions.

The sa account is disabled by default, but that won't stop you from changing its password.

ms sql allows you to reset the sa password through its properties by right clicking and selecting properties from the context menu.

On the general tab, you will see a field for entering a new password, the only thing to note is that if the Require the use of a password policy checkbox is checked, you will have to come up with a strong password that meets security requirements, namely

Must be a capital letter in the password
Must be a small letter in the password
There must be a special character or number in the password

If you uncheck the box, you can set a new password and save it. The password for the sa user in sql has been changed.

The only thing is, if you want to use the sa account, then you need to enable it, to do this, go to the status item and specify Login Name Enabled.

Another nuance, you remember that you have Windows authentication, which means that this is not suitable for us for sa. Right click on the server name at the top of the hierarchy and select properties.

On the Security tab, select the SQL Server and Windows Authentication option. Now you can login with sa user in sql.

If, when you try to log in, Management Studio gives you an error 233 that the connection to the server was successfully established, but then an error occurred while logging in, then do the following.

Open Start > Control Panel > Administrative Tools > Services and restart the SQL Server service.

Then the connection is successful and without errors.

Change sa password in sql via command line

To reset the sa password in sql using the command line, use the commands.

With this command you will see all available MS SQL servers and their SPNs

sp_password NULL,<вставьте_новый_пароль_тут>, 'sa'

If the message Password validation failed. The password does not meet Windows policy requirements because it is too short. Then set a stronger password.

All after that you will reset the sa password in sql.

Another way to use osql is like this

cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn then we try to connect under a trusted OS account

osql.exe" -S (local)\your server name -E

And the last frontier
ALTER LOGIN SA WITH PASSWORD='new_password'
it will replace the password with new_password

Using Asunsoft SQL Password Geeker

There is a utility Asunsoft SQL Password Geeker, it is paid, but able to perform the task. Run it, click Browse, then follow the path C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA and open master.mdf

now to reset sa password in sql select it and click Reset.

Change sa password in exclusive mode

There is a fourth way to change the password from sa, and it consists in running MS SQL in single-user mode (single-user mode).

The first is to stop MS SQL Server, you can through the services, or you can also from the command line

net stop MSSQLSERVER

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQLSERVER

Now you need to set the parameter in the line ImagePath-m will just talk about single-user mode. I got it like this

"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m -s MSSQLSERVER

Now start MS SQL with the command

net start MSSQLSERVER

SQL is now running in single user mode and allows any member of the machine's local Administrators group to connect to the SQL Server instance with sysadmin privileges, but SQL needs to be told to do so. You can view the operating mode in the properties of the service.

On the command line we write

cd C:\Program Files\Microsoft SQL Server\110\Tools\Binnsqlcmd.exe: EXEC sp_addsrvrolemember "servername\username", "sysadmin"

We restart the service, do not forget to remove the -m parameter in the registry later. Password reset to user sa in sql.

Hello everyone, today I will tell you how to change or reset sa password sql server. Let me remind you that sa is a user login in MS SQL, which has the highest privileges by default, the account itself is local, often even turned off, but nevertheless often used. It may happen that you forgot the password from it and thereby lost access to the databases, today you will learn how to bypass this and restore access to your databases.

sp_password NULL,<вставьте_новый_пароль_тут>, 'sa'

If the message Password validation failed. The password does not meet Windows policy requirements because it is too short. Then set a stronger password.

All after that you will reset the sa password in sql.

Another way to use osql is like this

then we try to connect under a trusted OS account

osql.exe" -S (local)\your server name -E

And the last frontier
ALTER LOGIN SA WITH PASSWORD="new_password"
it will replace the password with new_password

Using Asunsoft SQL Password Geeker

There is a utility Asunsoft SQL Password Geeker, unfortunately paid, but able to perform the task. Run it, click Browse, then follow the path C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA and open master.mdf