If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Prevent User from Changing Password

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2008
Location: Iasi, Romania
Posts: 460
Make sure that the login does not have ALTER ANY LOGIN permission.
__________________
Florin Aparaschivei
DB2 9.7 on Windows
Iasi, Romania
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2008
Location: Iasi, Romania
Posts: 460
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2008
Location: Iasi, Romania
Posts: 460
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #12 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,387
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
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Nov 2008
Posts: 7
re: Prevent User from Changing Password

That did the trick. many thanks.
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 1
Anyone know how is the performance for this trigger? Will it affected the SQL Server performance?
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On