SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Login account locked
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

prodigy2006
Yak Posting Veteran

66 Posts

Posted - 09/28/2009 :  17:03:36  Show Profile  Reply with Quote
Could anyone please explain the process to unlock a SQL Server login account?

Thanks,
Sunny

prodigy2006
Yak Posting Veteran

66 Posts

Posted - 09/29/2009 :  10:44:29  Show Profile  Reply with Quote
Could anybody please reply to this post.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 09/29/2009 :  11:18:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
Did you look in Books Online under "ALTER LOGIN"?
Go to Top of Page

prodigy2006
Yak Posting Veteran

66 Posts

Posted - 09/29/2009 :  12:40:58  Show Profile  Reply with Quote
I'm little confused here, is there a way to unlock a login without giving the password in the below Alter Login statement?

Alter login <loginname> with password=<passwood> unlock.

quote:
Originally posted by robvolk

Did you look in Books Online under "ALTER LOGIN"?

Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 09/29/2009 :  17:11:37  Show Profile  Click to see tripodal's MSN Messenger address  Reply with Quote
You are specifying the new password.

Assuming you have correct permissions you will not have a problem.

BOL quote
quote:

Requires ALTER ANY LOGIN permission.

If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

If the login that is being altered is a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

Resetting the password without supplying the old password.



Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION.


Changing the login name.


Enabling or disabling the login.


Mapping the login to a different credential.


A principal can change the password, default language, and default database for its own login.

Go to Top of Page

prodigy2006
Yak Posting Veteran

66 Posts

Posted - 09/29/2009 :  18:37:36  Show Profile  Reply with Quote
I want to unlock the developer's login without changing the password.
Will the below statement work:

Alter login <loginname> unlock
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 09/30/2009 :  11:42:32  Show Profile  Reply with Quote
nope. You need the password. Tell the developer to be more careful about locking himself out.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
808 Posts

Posted - 09/30/2009 :  18:16:14  Show Profile  Reply with Quote
If you really have to keep the password the same, then you can save the encrypted password value, unlock the account and change the password to a dummy string, then alter the login and change the password using the encrypted string.

-- First step is to get our hashed password value
SELECT cast(loginproperty('LoginNameHere', 'PasswordHash') As varbinary(256));

-- Save the above value somewhere, then unlock the account
ALTER LOGIN LoginNameHere WITH PASSWORD = 'DummyPasswordHere' UNLOCK;

-- Now, modify the login and reset the password with the old password
ALTER LOGIN LoginNameHere WITH PASSWORD = HashedPasswordHere HASHED;


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000