For development environment in your local machine, the old school approach seems to be more reasonable. You could have your environment setup in a script, wipe it out and recreate it again without disturbing the ActiveDirectory users etc.
Because I have not been using the sa (default administrator) user for long I have forgotten it. I search the Internet and find a way to recover the password.
C:\> osql -E -S .\SQLEXPRESS
1> exec sp_password @new='nupasswd', @loginame='sa'
1> exec sp_password @new='nupasswd', @loginame='sa'
2> go
3> exit
C:\>
Now try to login using the username 'sa' and the 'nupasswd'.
i did the thing u mentioned to change password for account 'sa' and it started giving the error: login failed for user 'sa'. Reason: the account is disabled. (Microsoft SQL Server, Error: 18470)
ReplyDeleteHi, first of all you need to enable the SQL Server Mixed Mode. Without enabling the Mixed Mode, you can only login to SQL Server using Windows authentication.
ReplyDeleteCould you check whether your Mixed Mode authentication is enabled on your system?
Otherwise, you might need to run something like this:
ReplyDeleteC:\> osql -E -S .\SQLEXPRESS
1> exec sp_password @new='nupasswd', @loginame='sa'
2> go
3> alter login sa enable
4> go
5> exit
C:\>
Thanks for this, worked perfectly! (first instance w/mixed mode)
ReplyDeleteDaniel,
ReplyDeleteThank you so much. The solution works like a charm. the "alter login sa enable" did the trick for me. I wish i had googled it earlier instead of running against the wall myself.
Hello
ReplyDeleteNice post however this didnt work for us, we get a logon denied error running osql -E -S .\SQLEXPRESS
our issue is slightly different as no domain users have logon access and the user cant remember the sa password.
What to do if my Windows account is locked up? How to fix it?
ReplyDeleteWhen your Windows account is locked up, find other people who have Administrator privilege to unlock your account. If you are the only Administrator and you are locked up, then it depends on which version of Windows you are in, there are some way to gain back the Administrator privilege (reinstall Windows and adding new Administrator user without deleting the previous installation, than create new user; or do some cracking to your system, etc).
ReplyDeleteRight Gareth, you need logon access to do that. May be you could ask help from the admins.
ReplyDeleteIf nobody has administrator access than the problem will not be limited to the SQL Server only but also to the whole server, as there are lot of maintenance things you really cannot do without the administrator privilege.