Post reply

Change Password

  • February 9, 2006 at 8:56 am

    #167091

    Dear All,

    I have a DB where my users have SELECT permission to all tables, INSERT-UPDATE-DELETE permission to selected tables.  What I need is to give my users the option to change their password.  (sp_password SP).  While trying to run this I get the following error: "Only members of the sysadmin role can use the loginame option. The password was not changed".

    Any idea as to how I should bypass this problem?  What kind of permissions should I grant to my users in order to achieve this task?

    Regards,

    Andreas

  • February 9, 2006 at 9:21 am

    #619833

    Is there any reason why your not/cannot use windows authentication?

    If so you wouldn't have to worry about this.

    But if not. There's really no "Easy" Way.

    Sql really wasn't desgined to be a user management system.

     

  • February 9, 2006 at 9:48 am

    #619841

    Dear Ray,

    Indeed, there is a reason for not using Windows Authentication.  I am not the Network Adminastrator and as far as I know, the environment (password-wise) is not the "most" secure one.  For my application it is important to give the users this functionality.

    Regards,

    Andreas

  • February 10, 2006 at 3:01 am

    #620048

    Andrea,

    It's probably just a parameter you've missed out. Users can run sp_password on their *own* account, but they must specify the old password.

    --Try this logged in as user1

    --This will fail:

    sp_password NULL, 'newpassword', 'user1'

    --This will succeed

    sp_password 'oldpassword', 'newpassword', 'user1'

  • February 10, 2006 at 3:13 am

    #620055

    Dear Joseph,

    I have already tried this.  It looks like the permissions that my users have are very limited and I think that  have to GRANT EXECUTE permission for the specific SP (sp_password).

    Thanks,

    Andreas

  • February 10, 2006 at 6:37 am

    #620082

    From BOL:"Execute permissions default to the public role for a user changing the password for his or her own login"

    If the user is already logged in, then the user can change his /her password --> sp_password 'current password', 'new password'.

    Members of the SA or security admin roles are the ones who can use the third parameter (login name) to change a users password.  

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Viewing 6 posts - 1 through 5 (of 5 total)

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