Skip to main content
Database Administrators

Return to Answer

added 97 characters in body
Source Link
ypercubeᵀᴹ
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

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:

  1. Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled

    Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled

  2. Start SQL Server instance in single user mode with following steps

    Start SQL Server instance in single user mode with following steps

    2.1 Open command prompt in admin mode

    2.1 Open command prompt in admin mode

    2.2 Run command ( I am running SQL Server 2012 instance)

    2.2 Run command ( I am running SQL Server 2012 instance)

    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s
    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s 
SQL2012 –m
    SQL2012 –m
  3. Open another command prompt in admin mode

    Open another command prompt in admin mode

  4. Connect to the SQL Server instance using command

    Connect to the SQL Server instance using command

    C:>sqlcmd –S(local)\instancename –E
    C:>sqlcmd –S(local)\instancename –E
  5. Create login

    Create login

    C:> CREATE LOGIN [loginname] FROM WINDOWS;
    C:> CREATE LOGIN [loginname] FROM WINDOWS;
C:>go
    C:>go
  6. Add login to sysadmin role

    Add login to sysadmin role

    C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]
    C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]
  7. Now disconnect from SQL Server instance

    Now disconnect from SQL Server instance

  8. Stop SQL Server instance by pressing cntl+C and ‘Y’

    Stop SQL Server instance by pressing cntl+C and ‘Y’

  9. Start SQL Server instance in muti-user mode and now you will be able to connect using newly created login

    Start SQL Server instance in multi-user mode and now you will be able to connect using newly created login

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

  1. Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled
  2. 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
  3. Open another command prompt in admin mode
  4. Connect to the SQL Server instance using command C:>sqlcmd –S(local)\instancename –E
  5. Create login C:> CREATE LOGIN [loginname] FROM WINDOWS; C:>go
  6. Add login to sysadmin role C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]
  7. Now disconnect from SQL Server instance
  8. Stop SQL Server instance by pressing cntl+C and ‘Y’
  9. Start SQL Server instance in muti-user mode and now you will be able to connect using newly created login

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:

  1. Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled

  2. 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

  3. Open another command prompt in admin mode

  4. Connect to the SQL Server instance using command

    C:>sqlcmd –S(local)\instancename –E

  5. Create login

    C:> CREATE LOGIN [loginname] FROM WINDOWS;
C:>go

  6. Add login to sysadmin role

    C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]

  7. Now disconnect from SQL Server instance

  8. Stop SQL Server instance by pressing cntl+C and ‘Y’

  9. Start SQL Server instance in multi-user mode and now you will be able to connect using newly created login

Source Link
Shankar Hendre
Shankar Hendre
  • 31
  • 2

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

  1. Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled
  2. 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
  3. Open another command prompt in admin mode
  4. Connect to the SQL Server instance using command C:>sqlcmd –S(local)\instancename –E
  5. Create login C:> CREATE LOGIN [loginname] FROM WINDOWS; C:>go
  6. Add login to sysadmin role C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]
  7. Now disconnect from SQL Server instance
  8. Stop SQL Server instance by pressing cntl+C and ‘Y’
  9. Start SQL Server instance in muti-user mode and now you will be able to connect using newly created login