Change Windows Authentication user for Sql Server Management Studio
Posted
by
Asmor
on Server Fault
See other posts from Server Fault
or by Asmor
Published on 2010-03-06T18:21:13Z
Indexed on
2012/03/30
17:33 UTC
Read the original article
Hit count: 319
mssql-ssms
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?
© Server Fault or respective owner