Access to SQL Server when all administrator accounts are deleted

If you removed Builtin\administrator from SQL Server which is the best security practice, and you forgot SA password, or SA account is disabled and there is no other Windows or SQL Server account on SQL Server with sysadmin privilege, starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

1. Stop all SQL Server related services (some SQL services such as SQL Agent will use up your only connection in single-user mode).

2. Start the SQL service with the -m parameter.
In SQL Server Configuration Manager, click SQL Server Services. In the right pane, right-click SQL Server (), and then click Properties. On the Startup Parameters tab, add -m; parameter (do not add a space between the -m; and the rest of the command line).

Or from CMD with 'Run as administrator' input:


This will put the SQL into Single User Mode, and SQL Server will only accept one connection.

Connect with SQL Server Management Studio (start Management Studio with "Run as administrator") and add login with sysadmin privilege, or use sqlcmd to connect to your server with the -E trusted connection option. You will be able to connect to the SQL Server as sysadmin if you are a local administrator.

Create your login and add it to the sysadmin role.

USE master
EXEC sp_addsrvrolemember @loginame=N'domain\username', @rolename=N'sysadmin'

Stop the SQL service, remove the -m parameter and restart the service. You should now be able to connect to the SQL Server normally.

If you get the error message:

Login failed for user 'domain\username'. Reason: Server is in single user mode. Only one administrator can connect at this time.

It means that there is something using your single connection. You need to find the service or connection that uses up the only connection and stop it before you can log in. Check SQL Agent, SQL Reporting Services, SQL Analysis Services...

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">