Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Unable to unlock user account in SQL server 2005 Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2008 12:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2008 2:02 AM
Points: 4, Visits: 2
I am unable to unlock user account in sql server 2005.
I searched in google and many forums suggest to use Alter login with password='Password' unlock in Management studio.

But the problem is I could not even login to the management studio express and the window was closed once the login failed.

Please help to resolve this.
Post #518782
Posted Wednesday, June 18, 2008 1:11 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
Are you logging on with the account that is locked or with a different account?


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #518791
Posted Wednesday, June 18, 2008 1:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2008 2:02 AM
Points: 4, Visits: 2
I am logging in with the account that is locked.I dont have the admin username and password.
Is there any other way to unlock ?
Post #518795
Posted Wednesday, September 24, 2008 12:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:15 AM
Points: 71, Visits: 43
I am having the same issue. I have a sql-authenticated id that is locked out due to numerous incorrect passwords. I go in as the system admin, change the password, and check that password must be changed at next login. However, once I hit ok with the Password Policy in place, I look at the user id, and the check is there for "Account is Locked" again. It's like I cannot unlock the id.
Post #575483
Posted Thursday, October 9, 2008 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2008 9:11 PM
Points: 2, Visits: 1
Same as grasshopper. The solution was to delete and recreate the account. Stupid. Very stupid.
Post #583569
Posted Thursday, October 9, 2008 1:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:15 AM
Points: 71, Visits: 43
That's exactly what I wound up having to do. And, yes, you're right. That's stupid!
Post #583571
Posted Thursday, October 9, 2008 3:33 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 5,872, Visits: 12,975
from memory the solution to this is something unexpected like untick the enforce password policy opton before unlocking the account on the status tab in login properties

---------------------------------------------------------------------

Post #583622
Posted Thursday, October 9, 2008 4:07 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:27 PM
Points: 4,064, Visits: 5,327
After going through this pain ourselves we've found the most straightforward way to unlock an account is to run this TSQL as sysadmin:

ALTER LOGIN TestLogin WITH PASSWORD = 'Test^123' UNLOCK

This changes the password and unlocks the account. Unchecking the "Login is locked out" box in SSMS is useless.

As for the OP, he'll have to find another admin login to use so he can unlock the account. If the Dedicated Administrator Connection is configured, the OP could use that to connect to the instance and unlock the login. See "Dedicated Administrator Connection" in BOL.


Greg
Post #583636
Posted Thursday, October 9, 2008 9:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2008 9:11 PM
Points: 2, Visits: 1
I managed to unlock an account today using SSMS. I first unlocked the account (I had to uncheck the password policy verification). The account was unlocked but I kept receiving failed login messages. Then I decided to retype the password and it worked. It seems that SSMS resets the password when you unlock and account. So you have to unlock and retype the password in a way very similar to the ALTER LOGIN command.
Post #583708
Posted Sunday, October 12, 2008 11:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 11, 2012 1:11 PM
Points: 60, Visits: 172
connect as an sysadmin to the SSMS collapse the security folder in the login folder right click on your particular login which is locked select the properties option.you see a select page pane.in the select page pane select the status and you see the status pane in that at the bottom you find an option login is locked out.check this option and the login which is locked gets unlocked.
Post #584657
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse