Sql-server – SQL Server 2014 All Administrator Accounts Disabled

access-controlconnectivitysql server

I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) and sa account on the production server! Please tell me a way that there is a way to access the server with administrator privileges! 🙁

Is it possible from an installation or some kind of command line etc?

Again, Administrator account IS disabled for SQL Server (but I have remote connection to server, the account itself is working…)

The Administrator account of the SERVER is AAAAA\Administrator, and the instance is SOMETHING\SOME_INSTANCE.

When I am trying to log in from the remote desktop (on the server itself) with the AAAA\Administrator account, it says its disabled:

Best Answer

You need to start SQL Server in single-user mode.

The documentation Connect to SQL Server When System Administrators Are Locked Out says:

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

See the page linked above for further considerations and step-by-step instructions.

Related Solutions

Sql-server – Delete SQL Server logins on replicated server

I dont see a need for 1.

You can use below script to move logins from one server to another. It uses SQLCMD and xp_cmdshell.

You can also look for a PowerShell option if you dont want to use xp_cmdshell.

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins from one server to another
-- Version  :: 1.0 for dba.stackexchange.com
--  Date    :: 05/24/2013
-- Change your paths for the output from D:\logs\ to whatever suits your need

declare     @dbid tinyint,
            @SQLText varchar(8000),
            @destserver varchar(255),

set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)

SQL Server – Granting Permissions on Service Accounts for Relocation

I was able to get guidance from the following MSDN link: Configure Windows Service Accounts and Permissions. The pertinent section (bookmarked in the link) is "File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups".

The short version for the solution is that either Read, Execute or Full Control are needed on the leaf-level subdirs under the system root (DATA, LOG, FTDATA, etc).

On my host, NTFS permissions weren't propagating from the mountpoint volume root or the folder by which the SYSDB LUN were mounted. Applying NTFS permissions on the leaf-level corrected the issue.

Best Answer

Related Solutions

Sql-server – Delete SQL Server logins on replicated server

SQL Server – Granting Permissions on Service Accounts for Relocation

Related Question