Can't connect using sa login and don't have any other administrator logins

I connected successfully in SQL Server Management studio using the 'sa' login and changed the authentication mode to Windows NT because I could not connect using my windows login but now I can't get any connection at all because 'sa' is no longer trusted.

I changed the loginmode registry setting to 2 and rebooted my PC but this has made no difference.

Can anyone help me get the 'sa' login back?

Unless you explicitly removed the default privileges (members of sysadmin) to builtin\Administrators, you should be able to connect to SQL Server using a Windows account that is member of the machine administrator s group.

NOTE: If you are using Windows Vista, you probably have UAC enabled, and you will need to use a full administrator token before connecting to SQL Server in the way I described above. You can use the right-click menu in Windows to “Run as Administrator” in order to get an unrestricted token before connecting to SQL Server.

Let us know if this information helped.

-Raul Garcia

SDE/T

SQL Server Engine

|||

I am on Windows XP, the engine is SQL Server 2000 and I am using SQL Server 2005 Management Studio. The problem started when I upgraded the software we are developing to the latest version which I suspect may have removed the existing SQL Server logins which did have full rights. It also changed the login mode from Windows Authentication to System Administration. My big mistake was to change the mode back to Windows Authentication in SQL Server Management Studio before checking the other logins and it was then that the problems started. My windows username has administrative rights but I still couldn't connect to the server using windows login.

I tried just now changing the LoginMode in the registry back to 1 and starting windows again but I still can't log in with 'sa'. I did notice that the default user registry key is set to 'guest' but don't know if that makes any difference.

I think that I may have to resort to re-installing SQL Server 2005. I would prefer not to do that but It doesn't really matter if I lose my local database as I do have a copy of it elsewhere.

Can you think of any other solutions?

|||

Try switching the LoginMode value in the registry to 2 (if I remember correctly, 2 should mean "Mixed mode"), restart SQL Server and try to connect with the SA login again.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, that worked perfectly.

FYI...

The reason it didn't work the first time is that I changed it in registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\LoginMode

which I assume is the key for SQL Server Management Studio 2005 but I didn't realise that there was another key, i.e.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode

which would be for the SQL Server 2000 engine.

Anyway, I have learned my lesson now and am going to add my windows login.