When all windows user accounts with sysadmin role or sa account are disabled in SQL server then how to access the instance. Below are the steps to solve this problem
- Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled
- Start SQL Server instance in single user mode with following steps 2.1 Open command prompt in admin mode 2.2 Run command ( I am running SQL Server 2012 instance) C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s SQL2012 –m
- Open another command prompt in admin mode
- Connect to the SQL Server instance using command C:>sqlcmd –S(local)\instancename –E
- Create login C:> CREATE LOGIN [loginname] FROM WINDOWS; C:>go
- Add login to sysadmin role C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]
- Now disconnect from SQL Server instance
- Stop SQL Server instance by pressing cntl+C and ‘Y’
- Start SQL Server instance in muti-user mode and now you will be able to connect using newly created login