Re: losing sysadmin

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 11/12/03 

Date: Wed, 12 Nov 2003 15:55:17 +0530

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

Relevant Pages

  • Re: How to check user_id and password via ADO ?
    ... SQL Server, when in Mixed Authentication mode, automatically checks user's ... > Please tell me how can I check the user_id and password while user logins? ...
    (microsoft.public.sqlserver.security)
  • RE: security
    ... SQL Server has two authentication mode: ... Mixed Authentication mode. ... administrator permission. ... Server however he need to be assigned more sql server permissions to ...
    (microsoft.public.sqlserver.setup)
  • How to set SQL Server only accept SQL Server Authentication mode?
    ... How to set SQL Server only accept SQL Server Authentication mode? ... Our company will not use mixed authentication mode, how to switch SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: security
    ... using Windows Authentication. ... SQL Server has two authentication mode: ... Server however he need to be assigned more sql server permissions to ...
    (microsoft.public.sqlserver.setup)
  • RE: How to access SQL Server from a non-privileged domain account
    ... account to be able to access the SQL Server. ... Since your SQL Server 2005 has been running with Windows Authentication ... Contact your network administrator to add your domain account to the ... Change your SQL Server 2005 authentication mode to Mix mode so that you ...
    (microsoft.public.sqlserver.setup)