How to access Microsoft SQL Server instance when you are locked out

You can still connect to the Microsoft SQL Server 2014 as a member of the sysadmin server role using single-user mode when you are locked out of the database.

Log in to the MS SQL Server.

Open SQL Server 2014 Managment Studio.

Username and password to the CRM database instance is not known, local administrator cannot access it too.

Open SQL Server 2014 Configuration Manager.

Identify database instance you want to gain access to. Stop this SQL Server Service instance.

Add the -m to startup parameters to start an instance of SQL Server in a single-user mode.

Start the SQL Server Service instance.

Open Command Prompt.

Use sqlcmd command to connect to the SQL Server Service instance as a member of the sysadmin server role.

Add local administrator to the sysadmin server role.

EXEC sp_addsrvrolemember 'DB\Administrator', 'sysadmin';
GO

This stored procedure will be removed in a future version of Microsoft SQL Server, so in case of problems use the following Transact-SQL.

CREATE LOGIN [DB\Administrator] FROM WINDOWS;
GO

ALTER SERVER ROLE sysadmin ADD MEMBER [DB\Administrator];
GO

Open SQL Server 2014 Configuration Manager.

Stop the SQL Server Service instance.

Remove -m option from the startup parameters to resume normal operations.

Start the SQL Server Service instance.

Open SQL Server 2014 Managment Studio.

Log in to SQL Server Service instance.

Success.


Additional notes

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system.