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

    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
    484
    Make sure that the login does not have ALTER ANY LOGIN permission.
    Florin Aparaschivei
    DB2 9.7 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
    484
    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 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
    484
    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 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,499
    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
  •