Exec sp_password from Access db

  • Patrice Peterson

    SSC Rookie

    Points: 39

    I have an AccessXP db which connects to a SQL db.  I would like my SQL users to be able to change their SQL password from an Access form.  I have read the BOL documentation for sp_password but still don't know how to access this stored proc from my Access db.  Does anyone have an example? 

    Thanks in advance.

  • Y. Kikuchi

    SSC Enthusiast

    Points: 123

    You write a pass-through query with the following syntax:

    Exec sp_password <old password>, <new password>

    Example:

    Exec sp_password cathasninelives, dogjumpsoveralazyfox

    The above just works. If the password begins with a number you have to enclose them in quotation marks (either single or double) otherwise you don't need to enclose them in quotation marks.

    If you are an SA and want to change another user's password, then you add user_id like this:

    Exec sp_password <old password>, <new password>, <user_id>

     

  • Admingod

    SSCertifiable

    Points: 5664

    I have question, when i run the exec sp_password syntax it works fine. However, i have server level trigger which i am auditing security access changes, so in order to work this i have to grant additional permissions like View server state and sp_send_dbmail. Which i would think is not appropriate to grant access to every user. Any advise? Thanks in advance.

  • WendellB

    SSCrazy Eights

    Points: 8623

    Since several weeks have passed, I hope you have sorted this out.  First of all, it's not clear whether you are using Integrated Security, or Standard Security.  Your question suggests you are using standard security, which means there is substantial administrative overhead in managing new users and departing users, and is generally agreed to be less secure than the integrated version which uses the network login.  In that case, the user can change their own password, external to the Access front-end, by simply changing their server login password on the Windows domain.  Giving users the ability to do SQL any admin functions is a scary concept in my view.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

Viewing 4 posts - 1 through 4 (of 4 total)

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