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.
Retart MSSQLSERVER in single-user mode
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
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???