2014 SSMS - SA Account password changing on it's owm - how to get rid off this issue.

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  March 23, 2016 at 8:42 am

  #1867106

  There was a forum discussion a few years back on how to prevent users from changing their SQL Server account passwords.

  https://social.msdn.microsoft.com/Forums/en-US/237e5b91-c5ca-49b1-980b-1e0429279231/prevent-sql-login-password-change?forum=sqlsecurity

  Consider something like the following. What it does it create a server level DDL event trigger that is fired whenever an account is altered in some way. If the current login is 'SA', then the operation is aborted.

  CREATE TRIGGER trg_prevent_sa_password_change

  ON ALL SERVER

  FOR ALTER_LOGIN

  AS

  IF ORIGINAL_LOGIN()= 'SA'

  Begin

  PRINT 'trg_prevent_sa_password_change:'

  + ' Altering of SA account is forbidden.'

  + ' Please contact database administrator.'

  ROLLBACK;

  end

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  March 23, 2016 at 12:42 pm

  #1867204

  If you have power users or 3rd party applications that insist on connecting as 'SA', then you can fake them off by renaming original 'SA' account, and then recreating 'SA' with reduced permissions.

  use MASTER

  go

  alter login sa DISABLE;

  go

  alter login sa with name = [sa_bak];

  go

  create login sa

  with PASSWORD = 'F5G8T9V0K1'

  , DEFAULT_DATABASE = master;

  go

  use Accounting;

  go

  drop user sa;

  go

  create USER sa for login sa;

  go

  exec sp_addrolemember 'DBPowerUsers', 'sa';

  go

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho