Change Windows Authentication user for Sql Server Management Studio

Question

We're using Sql Server 2005 with Windows Authentication setup. So normally, when you log in using e.g. Sql Server Management Studio, it forces you to log in at MACHINE_NAME\Username.

Anyways, on this one particular computer, the person said they had to make a new account called User01 to do something and showed me where she'd created it under security in the "master" system database. And so now when she logs in, it's listed as MACHINE_NAME\User01 (not the actual Windows user name). It's still set to Windows Authentication, though, and I'm unable to change the login name.

Now here's where the real problem comes in... I didn't realize that she was being logged in under this user name at the time, and I disabled it to see what would happen. Now I can't log into the server under her account.

I created a new account in Windows called test, and as expected SSMS had the username as MACHINE_NAME\test, and I was able to log in fine. However, the area where the User01 account was listed is not visible to me as far as I can tell and so I can't reenable it.

I also tried running the following query:

alter login User01 ENABLE

And got this error:

Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'User01', because it does not exist or you do not have permission.

So in a nutshell, ideally I'd like to reenable User01 somehow, just to get things back to where they used to be. Failing that, how can I force SSMS to log in using the Windows account name as it should be, rather than trying to use User01?

Solution

If you logged on as the Test user and you gave the Test user no other rights at the server level, then it won't be able to touch the User01 account. You need to be a member of the securityadmin or sysadmin role, have CONTROL SERVER rights, or something to that effect. What account did you originally use to connect and disable User01? If it was User01, and you have no other way to get in, you can do so by starting up in Single User mode. This will allow you to get back into SQL Server so long as you're logged in as a member of the local Administrators group for the computer.