CODEWITHSUNDEEP
sql-server
Deep Marsoniya
Deep Marsoniya

Reputation: 3

SQL Server password recovery

I am using SQL Server 2017 Management Studio and I lost my credentials and attempts to login will fail with Windows authentication. How to recover my password?

I found some method online, in which show first login with Windows authentication and then change password from property and I try it in other machine but it's not working.

Please help me.

Upvotes: 0

Views: 2628

Answers (3)

Nitish Saini
Nitish Saini

Reputation: 71

If you lost sa password and dont have windows authentication login,then sa password can be reset by following steps-:

1.Start SQL Server in single user mode. (a) open sql server configuration manager (b) Select your instance and click properties,and set -m of -f parameter in enter image description here startup parameter. (c) Click ok,and then restart SQL service .

2.Now all the users who are a member of the host’s local administrator group can connect to SQL Server instance and they will gain the privileges of server level role sysadmin which helps us to recover SA password.

3.Run SSMS (Sql Server Management Studio) as administrator,and login by windows authentication.

4.Now select security tab and select logins and find 'sa' user and select properties,and reset desired password.

5.Then again restart SQL SERVER INSTANCE by right clicking on it. enter image description here

6.Now sa password has been reset.

7.Now remove -f or -m parameter from SQL Server Configuration startup parameters.enter image description here

8.Again restart SQL SERVER SERVICE by right clicking on instance in configuration manager.

9.Now you have successfully reset sa password of SQL Server and check it by running SSMS and set SQL Server authentication mode in management studio.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Here's a batch file to perform the process.

Save this as admin2sysadmin.bat, and run it as a Windows Admin.

net stop mssqlserver 
net start mssqlserver /mSQLCMD 
sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssqlserver 
net start mssqlserver 
sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

Batch File to Grant Local Administrators a Sysadmin Login in SQL Server

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294267

Follow the steps described at Connect to SQL Server When System Administrators Are Locked Out. You will have to restart the service in administrative mode, login as a local administrator, and then add the necessary credentials.

Upvotes: 0

Related Questions