Trying to reset a user password error

Question

Post reply

Trying to reset a user password error

jrmtl

Ten Centuries

Points: 1075
More actions
June 29, 2009 at 9:03 am

#135473

Hi all,
Not sure what i ma doing wrong but this command doesn't work.
I am on the database i question
GO
ALTER LOGIN xxxxx WITH PASSWORD=’xxxxx’ MUST_CHANGE
GO
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
any ideas ?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Roy Ernest

SSC-Dedicated

Points: 38780

June 29, 2009 at 9:07 am

#1016892

Is this a SQL User or windows user?

-Roy

jrmtl Ten Centuries Points: 1075 More actions · Answer 2

tahnsk for response !

it's a sql user for one of the db's

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

I dont see anything wrong with the script. The script is what is shown in BOL as well.

Is CHECK_EXPIRATION and CHECK_POLICY set to ON for this user? MUST_CHANGE should have that preset.

-Roy

jrmtl Ten Centuries Points: 1075 More actions · Answer 4

I need to reset urgently but nothing works 🙁

how can i check those user credentials... what is BOL ?

GO

ALTER LOGIN [xxx] WITH PASSWORD = 'xxxx' MUST_CHANGE

GO

Doesn't work either even with brackets

Should I be on the master db query window ?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

BOL = Books On Line

You have to be on Master also you should have rights to do it as well.

-Roy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

jrmtl (6/29/2009)
I need to reset urgently but nothing works 🙁
how can i check those user credentials... what is BOL ?
GO
ALTER LOGIN [xxx] WITH PASSWORD = 'xxxx' MUST_CHANGE
GO
Doesn't work either even with brackets
Should I be on the master db query window ?

BOL = Books Online, the SQL Server Help System. Press the {F1} function key while in SSMS (SQL Server Management Studio) and that will bring it up.

As far as I can tell, you syntax is correct. I don't see anything wrong.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You don't need to be in the master database to execute the ALTER LOGIN statement, I just tested that on one of my SQL test logins.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Found this in BOL:

A known issue in Windows Server 2003 might prevent the bad password count from resetting after the Account Lockout threshold has been reached. This could cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON. For more information about the Account Lockout threshold, see Microsoft Knowledge Base article 818078: Your User Account May Be Prematurely Locked Out.

jrmtl Ten Centuries Points: 1075 More actions · Answer 9

pardon my ignorance...:)

Even in Master it gives me the same error, is it really a syntax issue ?

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'LOGIN'.

very frustrating 🙁

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Have you tried changing the password using the GUI tools?

jrmtl Ten Centuries Points: 1075 More actions · Answer 11

So Sorry I just realized that that particular Db is on a sql2000 server.nder it doesn't work 🙁

What is the exact syntax for sql2000?

soemthing like sp_changepassword

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

From BOL:

sp_password [ [ @old = ] 'old_password' , ]
{ [ @new =] 'new_password' }
[ , [ @loginame = ] 'login'

jrmtl Ten Centuries Points: 1075 More actions · Answer 13

I guess we have to be logged in as SA

I have admin rights and I execute

exec sp_password 'password', 'user'

and it tells me that MYuser doesn't exist?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

jrmtl (6/29/2009)
I guess we have to be logged in as SA
I have admin rights and I execute
exec sp_password 'password', 'user'
and it tells me that MYuser doesn't exist?

No, you didn't read the syntax for sp_password. You aren't specifying the parameters, so 'password' is the old password and 'user' is the new password and you aren't providing the user. If you are only providing newpassword and username, you need to specify the parameters to the proc.