Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2008
    Posts
    7

    Unanswered: Prevent User from Changing Password

    Hi,

    Is there a way to prevent a particular user with SQL login from changing their password?

    we have an application where we only want to prevent a certain user from changing their password but do not want to use windows authenthication?

    Thanks

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    533
    Make sure that the login does not have ALTER ANY LOGIN permission.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Nov 2008
    Posts
    7

    re: Prevent User from Changing Password

    I have issue the following command:

    use master

    REVOKE ALTER ANY LOGIN FROM "<login>" CASCADE

    GO

    The user is still able to change his password.

    Could you advise further?

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    533
    Oh, from MSDN:
    A principal can change the password, default language, and default database for its own login.

    CREATE TRIGGER trg_alter_login
    ON ALL SERVER
    FOR ALTER_LOGIN
    AS
    PRINT 'You cannot alter a login'
    ROLLBACK;

    This will affect everyone, including you. But you will use DISABLE TRIGGER when need it.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Nov 2008
    Posts
    7

    re: Prevent User from Changing Password

    hi,

    is there no way to create a custom policy?

    in oracle, i can achieve the same thing by using a profile. is there something similar?

    Thanks

  6. #6
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    I will continue to aflorin27

    CREATE TRIGGER trg_alter_login
    ON ALL SERVER
    FOR ALTER_LOGIN
    AS
    IF (ORIGINAL_LOGIN()= 'test' AND (APP_NAME()= 'GLOBAL GL')
    ROLLBACK;

    you can add this for specific user and application also

  7. #7
    Join Date
    Nov 2008
    Posts
    7

    re:Prevent User from Changing Password

    Thank you guys, i believe im on the right track, however i will need a little bit more help.

    i am using the code from above however i get the error:

    Incorrect syntax near the keyword 'rollback'

    trigger syntax

    CREATE TRIGGER trg_alter_login
    ON ALL SERVER
    FOR ALTER_LOGIN
    AS
    IF (ORIGINAL_LOGIN()= '<login>'
    PRINT 'You cannot change password!'
    ROLLBACK;


    i would be grateful if you can assist

  8. #8
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    CREATE TRIGGER trg_alter_login
    ON ALL SERVER
    FOR ALTER_LOGIN
    AS
    IF ORIGINAL_LOGIN()= '<login>'
    PRINT 'You cannot change password!'
    ROLLBACK;

    Use this code then it will works

    Regards,
    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

  9. #9
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    533
    Quote Originally Posted by babulmiah View Post
    hi,
    in oracle, i can achieve the same thing by using a profile. is there something similar?
    SQL Server has nothing similar to Oracle's profiles. BTW, I do not think you can prevent a usr from changing its own password using a profile.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  10. #10
    Join Date
    Nov 2008
    Posts
    7

    re: Prevent User from Changing Password

    Perfect, thanks SJahanzaib . This works brilliant, an oversight from me with the brackets. Im more of an oracle dba then sql server.

    aflorin27, in oracle you can create a profile to prevent a user from changing their password. I have already implemented this.

    Thanks for you help guys.

  11. #11
    Join Date
    Nov 2008
    Posts
    7

    re: Prevent User from Changing Password

    Hi,

    Initially i thought that the code worked but it doesnt, it prevents other users from changing their passwords even though their is a IF condition.

    Do you know why this is?

    Thanks.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,673
    Provided Answers: 1
    You need a begin and end statement for the IF result. Without the begin and end, only the print statement will be gated.
    Code:
    CREATE TRIGGER trg_alter_login
    ON ALL SERVER
    FOR ALTER_LOGIN
    AS
    IF ORIGINAL_LOGIN()= '<login>'
      begin
        PRINT 'You cannot change password!'
        ROLLBACK;
      end
    Last edited by MCrowley; 04-19-11 at 14:37. Reason: Fixed code tags

  13. #13
    Join Date
    Nov 2008
    Posts
    7

    re: Prevent User from Changing Password

    That did the trick. many thanks.

  14. #14
    Join Date
    Jan 2013
    Posts
    1
    Anyone know how is the performance for this trigger? Will it affected the SQL Server performance?

  15. #15
    Join Date
    Feb 2013
    Posts
    1
    I can't speak to the performance, but I would like to suggest a slightly different methodology. The code previously posted prevents a user from changing their own password or from changing any other user's password even in they have the rights to do so.

    What I needed was to prevent any user from changing a specific user's password. Below is the only way I found I could do it.

    Code:
    CREATE TRIGGER [trg_alter_login]
    ON ALL SERVER
    FOR ALTER_LOGIN
    AS
    DECLARE @LoginName nvarchar(255)      
    SET @LoginName =  EventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)')
    IF @LoginName like '<login>' 
    
      begin
        PRINT '<login> password cannot be changed.'
        ROLLBACK;
      end
    
    GO
    One thing of note: this code will result in an error being thrown for interrupting the transaction.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •