Post reply

Password changes

  • November 13, 2019 at 6:22 pm

    #3697440

    I would like my SQL users to be able to change their SQL password.Using below stor proc it can be done.

    Exec sp_password <old password>, <new password>

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

    • This topic was modified 5 years, 9 months ago by Admingod.

  • November 14, 2019 at 7:10 pm

    #3697777

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • November 15, 2019 at 4:40 pm

    #3698112

    Any advice please?

  • November 15, 2019 at 4:57 pm

    #3698118

    I think you can put an EXECUTE AS clause in your trigger definition.  Alternatively, use a SQL Audit instead of triggers.

    John

  • November 15, 2019 at 7:23 pm

    #3698153

    In order to give the stored procedure permissions that you don't want the calling user to have, it would help to use a certificate to sign the procedure.

    Here's an article showing and example of one for AdventureWorks:

    https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate?view=sql-server-ver15

     

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

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