Verify Password before changing password

Hi Everyone,

     I've been doing some updating on an application and ran into a small problem.

     Capability - Allow the user to change their password via the frontend application.  All logins are SQL Server logins.

     I created a stored proc back in 2004 and used the following statement

     EXECUTE master.dbo.sp_password @strOldPassword, @strNewPassword

     This has worked fine (originally created in SQL Server 2000) and will probably work for the foreseeable future.  I was doing some reading about changing passwords and according to Microsoft the sp_password will be deprecated at some point in the future and we should use ‘ALTER LOGIN’ instead.  So being the good little developer I am, I decided to give it a try.  

     I modified my stored procedure to use ‘ALTER LOGIN’ using the parameters passed in (current password and new password).  I was getting the current user from SQL.  Low and behold ‘ALTER LOGIN’ doesn’t like parameters.  So I converted my SQL to Dynamic SQL, see below.

SET @strSQL = 'ALTER LOGIN ' + @strCurrentUser + ' WITH PASSWORD=''' + @strNewPassword + ''' OLD_PASSWORD=''' + @strOldPassword + ''''

SELECT @strSQL
EXECUTE(@strSQL)

     The above worked great.  By the way, the SELECT @strSQL is only there for testing.

     The problem showed itself when the old password for the user isn’t correct.  The stored procedure throws a hard error, telling you the user doesn’t exist or you don’t have permission.  I could have captured that error and moved on, but I thought there should be a way to check the old password first before changing it.  I was reading the ‘Guru’s Guide To Transact-SQL’ earlier and saw the PWDCOMPARE and when I did a search tonight the same topic came up.  Perfect, my problems were answered.  So I modified my SQL to incorporate this undocumented function.

          --First we need to see if the old password is correct for this user
               IF (SELECT PWDCOMPARE(@strOldPassword, password) FROM master..syslogins WHERE name = @strCurrentUser) = 1
            
                    BEGIN
       
                         SET @strSQL = 'ALTER LOGIN ' + @strCurrentUser + ' WITH PASSWORD=''' + @strNewPassword + ''' OLD_PASSWORD=''' + @strOldPassword + ''''

                         SELECT @strSQL
                         EXECUTE(@strSQL)

                         If @@ERROR<>0
                              RETURN 1
                         ELSE
                              RETURN 0
                    END

          ELSE
               --SELECT PWDCOMPARE(@strOldPassword, password) FROM master..syslogins WHERE name = @strCurrentUser
               RETURN 3

     This worked perfectly as long as the user executing the stored procedure had a role that would allow them to execute PWDCOMPARE.   So, thinking back to some changes I had made today on another proc, I added the….

WITH EXECUTE AS N'SecurityUser'

      … to the stored procedure.  Unfortunately, the stored procedure always returns a 3.  It seems that the PWDCOMPARE doesn’t like to be executed using the ‘WITH EXECUTE’.  I’ve tried giving the user, ‘SecurityUser’, the roles of securityadmin, serveradmin and sysadmin, but to no avail.  I only want ‘SecurityUser’ to have enough rights to execute this proc.

     Any help would be greatly appreciated.  

     All I’m trying to do is to allow the users to change their password, but I want to verify their password first.  If there is a different path, I’m all ears (eyes).

     I hope I’ve given enough details.  

Thanks,
Mike