none
How to set up “log on” to the SQL Server running on my computer. RRS feed

  • Question

  • Because of the corruption of the personal account I have been using, I can no longer access to my SQL Server using “Window Authentication.” Therefore, I should like to set up a new “log on” as a Microsoft administrator using SQL Server Configuration Manager. As I display the menu of the running Server, I see the “Local System” checked.  I also see the “This Account” unchecked with the login name box and the password box. How can I set up the new “Window Authentication” from the Microsoft administrator account? In case this is not possible, how can I set up “SQL Server Authentication” with this menu? Please instruct me. Thank you.


    tatsuo nishimura manabe

    Saturday, June 29, 2019 2:52 PM

All replies

  • You can't set up logins in SQL Server from SQL Server Configuration Manager; all you can set is the service account.

    To create new logins, you need to log on to SQL Server. If you have lost access because the corruption of your account, you can start SQL Server in aingle-user mode. THen you can connect as a Windows administrator and be sysadmin in SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 29, 2019 8:27 PM
  • Dear Erland

    Thank you very much for your advice. Unfortunately, I do not know how to start SQL Server in a SINGLE-USER MODE. So far, all I can do is to start running or stopping, using SQL Server Configuration Manager. Do you suggest that I use "SQL Server Installation Center" to start?  (...Center>Installation>New Installation or add features to an existing installation>browse)??     


    tatsuo nishimura manabe

    Sunday, June 30, 2019 9:21 PM
  • So far, all I can do is to start running or stopping, using SQL Server Configuration Manager.

    One way to start SQL Server in single-user mode is to add the -m startup flag using SQL Server Configuration Manager. See steps 4-6 of this documentation page for step-by-step instructions for your case:

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out

    You can then add the local administrator account as a normal sysadmin role member by running these commands from an administrator command prompt"

    SQLCMD -E -Q"CREATE LOGIN [BUILT IN\Administrators] FROM WINDOWS;"
    SQLCMD -E -Q"ALTER SERVER ROLE SYSADMIN ADD MEMBER [BUILT IN\Administrators];"

    Then, follows steps 10-12 to remove the -m parameter. You will be able to connect as a local administrator going forward without starting in single-user mode.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, June 30, 2019 9:54 PM
    Moderator
  • Dear Dan

    Thank you very much for your help. Although I could start the SQL Server in a single user mode, I am stuck at point 8). (Single mode does now allow multiple administrators to access.) I do not know how to follow the following instructions.

    Connect with Object Explorer using Windows Authentication (which includes your Administrator credentials). Expand Security, expand Logins, and double-click your own login. On the Server Rolespage, select sysadmin, and then click OK.

    I see Object Explorer only in SQL Server Management Studio and you can clicking only “connection” there, which simply displays the login menu. There I see no “Security>Logins on that menu. Thank you for you further help. tim

    tatsuo nishimura manabe

    Wednesday, July 3, 2019 9:31 PM
  • Close SSMS. Open a command line window. Run

    SQLCMD -S .\INSTANCENAME -A

    If is a default instance, just type SQLCMD -A.

    At the prompt type

    CREATE LOGIN [DOMAIN\USER] FROM WINDOWS
    go
    ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\USER]
    go

    DOMAIN\USER is the Windows account you want to use as admin.

    Once you have done this, go to SSCM, remove the -m option and restart.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 3, 2019 9:54 PM