Forgot my SA password, and I don't have sysadmin rights...

So, say you're at a new client site, or maybe even an old client site, and someone brings to your attention that they have this old SQL server that's used almost every day, but the guy who set it up left six years ago...

And he/she was the only one set as sysadmin, and nobody knows the SA password.

Believe it or not, this happens more often that I could have imagined. I'll share my method of gaining access to a "lost" SQL server.

First I want to start the SQL services in maintenance mode.

Go into services and identify the exact name of the SQL Service:

Then open up a command prompt (as administrator):

NET STOP MSSQL$INSTANCENAME

Then start SQL Service in maintenance mode with the /m switch:

NET START MSSQL$INSTANCENAME /m

Now using the command prompt connect to sql using the following command:

osql -S server/instance -E

Now that you've established a connection, you can start executing the right T-SQL commands:

To reset the SA Password:

EXEC sp_password NULL, 'password', 'sa';

GO

To add your windows login:

CREATE LOGIN [domain\login] FROM WINDOWS;

GO

Then add it to the sysadmin group:

SP_ADDSRVROLEMEMBER 'domain\login, 'SYSADMIN'

GO

#sqlserver #security