Reset your SQL Server login

Reset Aug 17, 2011

Recently I faced a situation where the SQL Server was setup but no one knew the login, and the SQL server was not configured in Mixed Mode Authentication, so how do you recover from this situation other than re installing the instance,
Stop the running instance and then open up command prompt in Binn directory on the instance which would be something like

c:\Program Files\Microsoft SQL Server\MSSQL...\Binn 

Run the following command

sqlservr.exe -m"SQLCMD"

This would start the instance in single user mode, now open up another command prompt in the same directory
and then run "SQLCMD"
and execute the following statements based on requirement

If you want to add a windows user use

create login [DOMAIN\USERNAME] from windows;
EXEC sys.sp_addsrvrolemember @loginame = N'DOMAIN\USERNAME', @rolename = N'sysadmin';

To add a SQL Server Login use (remember this works only if mixed mode authentication is enabled)

EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin';

now exit SQLCMD and also do CTRL+C on first window to stop instance and restart the instance in regular mode.