Execute SP_password

[ano1optimist]

I'm using Access 2000 and have tried Access 2003 front-end to execute
the stored procedure sp_password on SQL Server 2000. I continue to
receive the following message: "The old (current) password was not
correct. Password not changed." If I go into query analyzer and run
an execute @old, @new, @loginame with the same values as passed to my
command collection in Access, it works just fine. I'm using Windows
Authentication and my server is defined for SQL Server and Windows
authentication. I have tried using the sqladmin account and have
received the same error message. I have verified the old password and
I know it is correct because I am able to change the password by
executing the same stored procedure in query analyzer. Why are the
results different? How can I resolve this problem?

[Erland Sommarskog]

ano1optimist (an**********@aol.com) writes:

I'm using Access 2000 and have tried Access 2003 front-end to execute
the stored procedure sp_password on SQL Server 2000. I continue to
receive the following message: "The old (current) password was not
correct. Password not changed." If I go into query analyzer and run
an execute @old, @new, @loginame with the same values as passed to my
command collection in Access, it works just fine. I'm using Windows
Authentication and my server is defined for SQL Server and Windows
authentication. I have tried using the sqladmin account and have
received the same error message. I have verified the old password and
I know it is correct because I am able to change the password by
executing the same stored procedure in query analyzer. Why are the
results different? How can I resolve this problem?

Obviously the call that is generated from Access, is not the same
that you make from Query Analyzer.

I would use the Profiler to see what is actually being sent to SQL
Server.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ano1optimist]

In query analyzer, this is my statement:

execute master.dbo.sp_password 'OLD', 'NEW', 'XYZ'

In Access, here is my code:

Set Cmd = New ADODB.Command
Set Prm = New ADODB.Parameter
Set Cmd.ActiveConnection = CnnAdo
Cmd.CommandText = "master.dbo.sp_password"
Cmd.CommandType = adCmdStoredProc

Set Prm = Cmd.CreateParameter("@old", adVarChar, adParamInput, 10,
Me!txtoldpassword)
Cmd.Parameters.Append Prm

Set Prm = Cmd.CreateParameter("@new", adVarChar, adParamInput, 10,
Me!txtnewpassword)
Cmd.Parameters.Append Prm

Set Prm = Cmd.CreateParameter("@loginame", adVarChar, adParamInput,
10, Me!txtusername)
Cmd.Parameters.Append Prm

Cmd.Execute , dbFailOnError

I've added a message box prior to the cmd.execute statement and it
shows the following:

@OLD = OLD
@NEW = NEW
@LOGINAME = XYZ

If I try to execute the statement in Query Analyzer twice, the second
time, it shows the following, as is should on the second try:

Server: Msg 15211, Level 16, State 1, Procedure sp_password, Line 58
Old (current) password incorrect for user. The password was not
changed.

This is the message I get whenever I execute the access command code,
regardless of first or second try.

So, how could I be executing different code? Please explain further
because this has really stumped me.

[ano1optimist]

I've tried running the Profiler to see what is happening but I've
never used that product before. The Loginames were the same, except
for case. I tried it again making sure that the loginnames were of
the same case and still got the same error message. Is this a timing
issue where the change hasn't been committed yet?

[Erland Sommarskog]

ano1optimist (an**********@aol.com) writes:

In query analyzer, this is my statement:

execute master.dbo.sp_password 'OLD', 'NEW', 'XYZ'

In Access, here is my code:
...
Server: Msg 15211, Level 16, State 1, Procedure sp_password, Line 58
Old (current) password incorrect for user. The password was not
changed.

This is the message I get whenever I execute the access command code,
regardless of first or second try.

I ran a variation of your code from Visual Basic:

oCommand.CommandType = adCmdStoredProc
oCommand.CommandText = "master.dbo.sp_password"
oCommand.Parameters.Append oCommand.CreateParameter( _
"@old", adVarChar, adParamInput, 10, "ggggg")
oCommand.Parameters.Append oCommand.CreateParameter( _
"@new", adVarChar, adParamInput, 10, "hhhhh")
oCommand.Parameters.Append oCommand.CreateParameter( _
"@login", adVarChar, adParamInput, 10, "lklklkl")
oCommand.Execute

And this worked the first time (but of course the second time). On the
first successful occassion there was no output, as it can be fairly
difficult to pick up messages that are not errors with ADO.

I'm a bit out of ideas, but one thing is to check is that you don't run
the Access code twice by mistake. And of course, that the old
password or login does not exceed 10 characters in length.

My suggestion to use the profiler was not entirely useful. Profiler
reconizes sp_password and sensors the parameter. You could could
however call a wrapper SP that calls sp_password and trace that
call.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ano1optimist]

I removed the dbFailOnError from the command execute line and changed
the code to use a wrapper stored procedure and everything works just
fine. I tried it again without the wrapper stored procedure and
continued to get the same old message about the old password not being
correct, even though I knew it was. Thanks for the info on the
Profiler, I'm fairly new to SQL Server so it will be a great help.