Track SQL Login changes

• naran.vekaria

  Grasshopper

  Points: 17

  More actions

  April 18, 2012 at 7:18 am

  #392053

  Hi,

  Every now and again the 'Enforce Password Policy' keeps getting unchecked on all our SQL logins on the server.

  Is there a way to track the changes? find out what/who is making the change?

  sys.sql_logins tells me the last modify_date but not who/what made the change.

  Thanks

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  April 18, 2012 at 9:42 am

  #1475428

  Policy Based Management can accomplish this.

  You can have the policy evaluated on change and either log or prevent the change from occuring if it doesn't meet the requirement.

  Here's some sample code.

  Declare @condition_id int

  EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'PolicyIsEnabled', @description=N'', @facet=N'ILoginOptions', @expression=N'<Operator>

  <TypeClass>Bool</TypeClass>

  <OpType>EQ</OpType>

  <Count>2</Count>

  <Attribute>

  <TypeClass>Bool</TypeClass>

  <Name>PasswordPolicyEnforced</Name>

  </Attribute>

  <Function>

  <TypeClass>Bool</TypeClass>

  <FunctionType>True</FunctionType>

  <ReturnType>Bool</ReturnType>

  <Count>0</Count>

  </Function>

  </Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT

  Select @condition_id

  GO

  Declare @object_set_id int

  EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'LoginsMustHavePolicyEnforced_ObjectSet', @facet=N'ILoginOptions', @object_set_id=@object_set_id OUTPUT

  Select @object_set_id

  Declare @target_set_id int

  EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'LoginsMustHavePolicyEnforced_ObjectSet', @type_skeleton=N'Server/Login', @type=N'LOGIN', @enabled=True, @target_set_id=@target_set_id OUTPUT

  Select @target_set_id

  EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Login', @level_name=N'Login', @condition_name=N'', @target_set_level_id=0

  GO

  Declare @policy_id int

  EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'LoginsMustHavePolicyEnforced', @condition_name=N'PolicyIsEnabled', @execution_mode=2, @is_enabled=True, @policy_id=@policy_id OUTPUT, @object_set=N'LoginsMustHavePolicyEnforced_ObjectSet'

  Select @policy_id

  GO

• naran.vekaria

  Grasshopper

  Points: 17

  More actions

  April 18, 2012 at 9:57 am

  #1475440

  Hi,

  Thanks for that I'll test this and use it to prevent it from occuring.

  Is there any way to check what may be changing the 'Enforce Password Policy' option?

  Thanks

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  April 18, 2012 at 10:26 am

  #1475472

  naran.vekaria (4/18/2012)

  ---

  Hi,

  Thanks for that I'll test this and use it to prevent it from occuring.

  Is there any way to check what may be changing the 'Enforce Password Policy' option?

  Thanks

  In the Sql Error log, when the policy is violated, it dumps out the ALTER LOGIN event data:

  Date4/18/2012 12:19:19 PM

  LogSQL Server (Current - 4/18/2012 12:00:00 PM)

  Sourcespid27s

  Message

  The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following:

  '<EVENT_INSTANCE><EventType>ALTER_LOGIN</EventType>

  <PostTime>2012-04-18T12:19:18.947</PostTime>

  <SPID>58</SPID>

  <ServerName>MYLAPTOP\R2</ServerName>

  <LoginName>MYDOMAIN\MyLoginName</LoginName>

  <ObjectName>testlogin</ObjectName>

  <ObjectType>LOGIN</ObjectType>

  <DefaultLanguage>us_english</DefaultLanguage>

  <DefaultDatabase>master</DefaultDatabase>

  <LoginType>SQL Login</LoginType>

  <SID>aHoANMYCI0iH9iXUa9g0zA==</SID>

  <TSQLCommand>

  <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/>

  <CommandText>ALTER LOGIN [testlogin] WITH PASSWORD=N'******'

  </CommandText></TSQLCommand>

  </EVENT_INSTANCE>

• naran.vekaria

  Grasshopper

  Points: 17

  More actions

  April 18, 2012 at 10:38 am

  #1475483

  Excellent.

  thanks again