Security – Lost sa password SQL Server – no Windows authentication – new roles created at command line do NOT appear

password-recoverySecuritysql server

Our support people changed the 'sa' password our SQL Server DB. They switched off Windows Authentication. Then the guy who did the work left.

So we have a production database that isn't being backed up and that we are locked out of. And over 10 years of production data in it.

I have tried the usual trick of adding new roles from the SQLCmd command line.

  1. Retart MSSQLSERVER in single-user mode

  2. from MS-DOS command line, issue "MSSql -S 127.0.0.1\MSSQLSERVER" and then at command-line:

    USE [master]
GO

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

EXEC master..sp_addsrvrolemember @loginame = 'BUILTIN\Administrators', @rolename = 'sysadmin'
GO

  3. Restart MSSQLSERVER as normal

But in SQL Management Studio the new users and new role assignments are nowhere to be seen.
Users

If I go to master.sys.server_principals I do not see the new logins.
server_principals table

If I try to add the role to an existing user in Management Studio, it tells me I don't have permissions. I'm logged in as a Windows Administrator but not 'sa' (because that's the password I'm trying to recover or reset):
No permission

I go back to MSSql command line and reissue the above commands and it says the principal already exists.
principal exists

There is no other instance of SQL on the machine. There is not other server on our network. There are no error messages that I can see anywhere. I am doing the above with full Administrator rights on Windows Server 2012 R2.

How come MSSql command-line creates the logins & assigns roles, reporting it's OK, but there are no error messages and nothing visible in SQL Server Management Studio. I've restarted MSSQLSERVER. I've restarted Management Studio.

Think I'm going crazy. Anyone have any ideas what to do???

Best Answer

I would recommend following the steps I note below to regain access to your data.

  1. Set the SQL Server instance back to Mix mode authentication.

You can do this by stopping the SQL Server instance and adjusting the registry key. Changing this registry is all SSMS did when the form DBA changed it. There is no issue with changing this registry key to set it back, just makes sure the instance is not running.

The registry key path should be something like this: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQLSERVER\MSSQLServer\LoginMode. You should find this set to 1 now for Windows Authentication. Change this value to a 2 for mixed mode.

  1. Install PowerShell module on the server or your client machine from the PowerShell gallery called dbatools.

Once you have the module installed you can utilize the command: Reset-DbaAdmin. This will prompt you to reset the "sa" account and let you regain access to the instance again.

#Install from the gallery
Install-Module dbatools

# Import module and run command
Import-Module dbatools
Reset-DbaAdmin -SqlInstance YourServerName
Related Topic