I tried to provide the user the possibility to change his password.
I have written a stored procedure which call sp_password :
EXEC master.dbo.sp_password @sOldPassword
I gave the execution rights to public roles for this stored procedure.
I run the stored proc from SQL Query Analizer using my user name ( admin rights) and it works.
I launch SQL Query Analizer using the user name and the new password.
I try to change once again the password from the launched SQL Query Analizer using the user name and the new password and it doesn’t work.
The error message is:
Seuls les membres du rôle sysadmin peuvent utiliser l'option loginame. Le mot de passe n'a pas été changé.
(Just the sysadmin role members can use the loginname option. The password hasn’t been changed).
Someone has an idea how I can do that and why it doesn’t work
I didn’t try to change somebody else password and Microsoft help says that I will be able to change my own password without admin rights.