Posted by You have installed SQL server, but you don’t remember the SA password you entered during the installation. Something that almost all system administrators have experienced at one time or another.
Luckily it’s no rocket science to reset the old or create a new SA account for SQL server. The only requirement is that you are a local administrator on that server.
Yesterday I did follow another “how to” which gave me the following error:
Server is in single user mode. Only one administrator can connect at this time.
So I decided to turn on my brain on and do my own research.
First, stop all related SQL Services to make sure no processes are using the SQL engine.
With PowerShell:
|
1 |
get-service -displayname *SQL* | stop-service -force |
Then start SQL Server with the “single user mode” /m flag. With the /m flag its possible to point an application which can use the single user mode. Open command prompt (CMD) as Administrator:
|
1 |
Net Start MSSQLSERVER /m"SQLCMD" |
In the same command prompt, try to connect to your SQL instance with SQLCMD.
Run the following command to make sure the SQL account is enabled.
|
1 2 3 |
C:\Users\##>SQLCMD -Slocalhost ALTER LOGIN sa enable GO |
Now it’s possible to add another account to the sysadmin role.
Create a new SA login and add it to the sysadmin role, or check next paragraphs to add a Windows Account:
|
1 2 3 |
CREATE LOGIN SaNew WITH PASSWORD = 'NEWPASSWORD'; ALTER SERVER ROLE sysadmin ADD MEMBER SaNew GO |
It’s also possible to add a Windows security group or Windows Account.
|
1 2 3 4 5 6 7 8 9 |
CREATE LOGIN [SQL01\NotYourAdmin] FROM WINDOWS ALTER SERVER ROLE sysadmin ADD MEMBER [SQL01\NotYourAdmin] GO -- OR -- CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS ALTER SERVER ROLE sysadmin ADD MEMBER [BUILTIN\Administrators] GO |