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

 

gis8

 

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 😦

gis1

 

 

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.

-i Run the program so that it interacts with the desktop of the specified session on the remote system. If no session is specified the process runs in the console session.
-s Run the remote process in the System account.

gis2

This opens up this license screen.  Click Agree.

gis3

 

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

gis4

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.

gis5

 

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

gis6

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.

gis7

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s