When you install SQL Server, you need to specify what account(s) or group(s) that you want to give administrative privileges. Way back when, it would be sufficient to be a local administrator on the Windows machine running the SQL Server service, but not any more.

Luckily, there’s a solution.

This solution applies when you’ve set Windows Authentication-only as your login method, or if your only admin account is sa and you’ve forgotten the password. You will need to be an administrative user with local access to the machine running the SQL Server.

Step-by-step

• Stop the SQL Server system service. You can do this by using the Services control panel or “net stop” in a command prompt.
• Open a command prompt (with administrative privileges).
• cd to the directory where sqlservr.exe resides. If you’re unsure, check the properties in the Services control panel, under “Path to executable”.
• Run the following command:

sqlservr.exe -c -m

• .. to start a single-user instance of your server. The -c switch bypasses a number of system service-related features. -m is the single-user admin mode.

• I also had to add -s instancename because my SQL Server uses a named instance.
• Wait a few seconds for SQL Server to start up – you should see a message like “SQL Server is now ready for client connections.”
• Now, open a new command prompt.
• Browse to the same directory that you used to start the server.
• Run sqlcmd -E, or if you have a named instance, sqlcmd -E -S .\instancename. The -E parameter means trusted connection (i.e. Windows authentication) and -S specifies the server and instance name.
• Now you should be logged in. Time to create your login.

    CREATE LOGIN [domain\user] FROM WINDOWS
    GO

• .. where domain is your machine or corporate domain where you are logged in, and user is your Windows username.
• After that, you’re going to assign permissions to your new login.

    EXECUTE sp_addsrvrolemember
        @loginame='domain\user',
        @rolename='sysadmin'
    GO

• Now you’ve added your Windows user as an administrative login on the server. You can log out from sqlcmd and close that prompt.
• In the prompt running SQL Server, hit Ctrl+C and confirm by pressying Y.
• Now you can start SQL Server normally in the Services control panel and log in using Windows authentication.