Locked out of SQL Server 2008 and prior

The deadline has passed for SQL Server 2008 and prior to be supported so now you have to migrate the databases to newer versions, but you cannot connect because the person who knows the SA password has left or it has been forgotten.  Using PSEXEC you can recover access and login!

We have tons of old and forgotten databases running these old versions of SQL Server, but nobody remembers the login credentials.  To get access, all you need to do is download PSTOOLS from Microsoft’s site, and run PSEXEC.  PSTOOLS has a bunch of nifty utilities so I highly recommend downloading it.

https://docs.microsoft.com/en-us/sysinternals/downloads/psexec

 

 

I currently have access to the server below, but cannot change my security because I do not have admin rights.  We also forgot the sa password 😦

 

 

PsExec to the rescue.  Open a command prompt with “Run as Administrator”.  Then run psexec with the following switches:  -s -i and specify the program you want to open.  In our case it is ssms.exe.  You can also use SQLCMD or OSQL if you prefer the command line.

This opens up this license screen.  Click Agree.

 

This will start up SQL Server Management Studio as the NT Authority\System user which has admin rights.

Now you have access to modify/create/delete users, databases, etc.  Below I find my username and give myself sysadmin rights.  When I am done, I close out of Management Studio.

 

This takes us back to the dos prompt, and we can close this program.

Now opening up SQL Server Management Studio, it shows my name and now I have sysadmin rights and can go about figuring out if the databases should be migrated or not.

This trick with Psexec does not work for databases greater than 2008 because the permissions are different when you install SQL Server.  In this case you will need to start the service in single user mode and make the change that way.

Now go get those databases migrated.