Re: losing sysadmin

From: Anand (anonymous_at_discussions.microsoft.com)
Date: 11/12/03

  • Next message: Anand: "Re: losing sysadmin (Thanks)" 
    Date: Wed, 12 Nov 2003 02:54:43 -0800

    Thanks Hari,

    >-----Original Message-----
    >Hi,
    >
    >You can edit the registry key ( This key will vary
    slightly for a default
    >instance of SQL server)
    >
    >\\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
    >Server\HARISQL\MSSQLServer
    >
    >Change the value for LoginMode key to 2 (Mixed mode).
    This will allow you to
    >login as SA.
    >
    >
    >Thanks
    >Hari
    >MCDBA
    >
    >"Anand" <anand_r@linuxmail.org> wrote in message
    >news:0c8b01c3a8d7$7bf3b4f0$a101280a@phx.gbl...
    >> Hello All,
    >>
    >> I was just experimenting this.
    >>
    >> 1)My SQL Server is in Mixed Authentication Mode.
    >> 2)I register my server using SQL Authentication (sa
    login)
    >> 3)I revoked the sysadmin role from
    BUILTIN/Administrators
    >> using
    >>
    sp_dropsrvrolemember 'BUILTIN/Administrators' , 'sysadmin'
    >> 4)Now I changed the Mixed Authentication Mode to Windows
    >> Only Authentication Mode
    >> 5)I deleted the server registration and re-registered
    >> using Windows Authentication
    >>
    >>
    >> Now the problem is that I can't create a new login, I
    >> can't see/change the properties of a login because
    >> now I don't have a sysadmin role. I can't even change
    >> the Windows Authentication mode back to Mixed Mode,
    >> because that also require a sysadmin role. So after
    >> struggling a lot I finally reinstalled the instance and
    >> now everything is working fine.
    >>
    >> My question is whether there is a workaround for this
    >> other than reinstalling.
    >>
    >>
    >> Thanks in advance
    >>
    >> Anand
    >>
    >>
    >
    >
    >.
    >

  • Next message: Anand: "Re: losing sysadmin (Thanks)"

    Relevant Pages

    • Re: Other way of Changing Password if does not have Server Role
      ... A user can change his password without being a member of the sysadmin role. ... You just have to login as the user and then omit the 3rd parameter ... Dejan Sarka, SQL Server MVP ...
      (microsoft.public.sqlserver.security)
    • Re: OK - Im stupid....How do you create a user?
      ... not a Windows login, you'll need to change the authentication mode SQL ... Server uses from "Windows Authentication" to "SQL Server and Windows ... Windows authentication mode if at all possible. ...
      (microsoft.public.sqlserver.security)
    • Re: losing sysadmin (Thanks)
      ... >login as SA. ... >> 1)My SQL Server is in Mixed Authentication Mode. ... >> 4)Now I changed the Mixed Authentication Mode to Windows ... >> now I don't have a sysadmin role. ...
      (microsoft.public.sqlserver.security)
    • Re: "Login failed for user (null) reason: not associated...
      ... You'd want to consider using DSN-less connections in your ... Then the DSN or login prompts are not the issue ... authentication mode and somewhat on your application. ... connect to SQL Server, ...
      (microsoft.public.sqlserver.security)
    • Re: How to login in MsSqlServer
      ... I would take a guess that you have SQL Server in Windows Authentication mode ... To alter this, without having to login, check out: ... > access right, only the sa have login access, Now I can't ...
      (microsoft.public.sqlserver.security)