If you happen to have lost your MSSQL SA credential or something goes haywire where you couldn't login as SA, you might think that the only way forward is to built everything from scratch.
Fortunately, MSSQL left a pretty cool trick to manually add sysadmin to the database. Here's how.
Firstly, you must have the following:
1. At least administrator account to the server you accessing. This could be local account, or domain account and it must in the Administrators group
2. Be prepared to have downtime to your database instance.
3. Get the id ready eg: contoso/admin01
Steps
1. Open SQL server configuration console
2. Stop the MSSQL server instance
3. Now, if you have SQL server agent, stop that as well
4. Now run command prompt as administrator
5. Navigate to your SQL instance Binn folder via 'cd' command. eg: c:/Program Files/Microsoft SQL/MSSQL/Binn
6. From there, execute sqlservr.exe with single user mode using this command: 'sqlservr.exe -m'
7. Now the SQL instance is running in single user mode. Open up another command prompt as administrator.
8. Type in these command 1 after another to add another sysadmin. Press Enter to go to next line
EXEC sp_addsrvrolemember 'contoso\admin01', 'sysadmin';
GO
9. You might don't receive any feedback from the command, but no worries, it's there *fingers crossed
10. Stop the instance by 'ctrl+c' and reply 'y' in the first cmd window
11. Now go back to SQL server configuration console, start the instance and server agent again
12. Run MSSQL studio, and login using windows authentication. You need to login the server using credential used previously eg: 'contoso\admin01'
13. Hopefully you can login now as system admin.
Hope this helps:)
