none
Alter login for SA??

    Pregunta


  • The sa (system administrator) login seems to be disabled for my SQL Server 2005.

    I don't have the permissions to change it from the "loging properties-tab".


    And as mentioned in an earlier thread, my windows authentication doesn't work for 2005...

     

    So, is there a way to enable the login for the sa user when your windows authentication is screwed up?
    SQLCMD??

     

    Regards,
    JOHAN

     

    martes, 04 de diciembre de 2007 17:02

Todas las respuestas

  • If your server only supports windows authentication, you first have to switch to mixed mode authentication. Otherwise, sa won't work.

     

    If you can connect to the server as a sysadmin, you can enable sa by issuing: ALTER LOGIN sa ENABLE.

     

    If you cannot connect to the server as a sysadmin because you locked yourself out somehow, then you can restart the server in single user mode and reconnect from a machine administrator account, using Windows authentication - you'll get sysadmin rights and can work to correct the situation.

     

    Thanks

    Laurentiu

    martes, 04 de diciembre de 2007 19:48
    Moderador
  • Thanks for the quick reply!!

     

    Right now it's just possible to log in using SQL Server Authentication.

    The windows authentication has stopped working. I get the message "Cannot generate SSPI context".

     

    The only user I can log in with is a general user, that lacks system admin rights.

    The login for the sa user is set to disabled.

     

    Moreover, it's all run on my local machine. Everything works fine for Enterprise.

     

    Thanks,

    Johan

    martes, 04 de diciembre de 2007 20:38
  •  

    Have you tried connecting locally as a machine admin with the server in single-user mode? If you are still getting the SSPI message, then I suggest searching the Data Access forum for similar issues - this message indicates a protocol issue - you're not even getting to the point where security kicks in.

     

    Thanks

    Laurentiu

    martes, 04 de diciembre de 2007 21:56
    Moderador
  • Yeah, just tried that. Still get the SSPI message I'm afraid.

    Guess I'll have to check out the other forum.

     

    Thanks,

    J.

    martes, 04 de diciembre de 2007 22:21
  • Hi,

    Try out the below blog link. This blog is for Resetting SA password, but you can also use this blog for enabling SA.

    http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx
    sábado, 08 de diciembre de 2007 5:48
    Moderador