create login: CHECK_POLICY=ON workaround ?

• Lynx_Kepler

  Old Hand

  Points: 363

  More actions

  September 2, 2011 at 8:32 am

  #243312

  Dear Fellows,

  I am using SQL Server 2005 Enterprise + Windows 2003 Server + a third-party application (I cannot change its code) that sends a plain CREATE LOGIN

  The login is created with the default CHECK_POLICY=ON

  I need all my logins in this database with CHECK_POLICY=OFF

  The only question is: how can I accomplish that ?

• Lynx_Kepler

  Old Hand

  Points: 363

  More actions

  September 2, 2011 at 9:06 am

  #1376490

  Answered by Herr Uwe Ricken at MSDN with the following script:

  DECLARE@stmtnvarchar(255)

  DECLAREc CURSOR

  FOR

  SELECT'ALTER LOGIN ' + QUOTENAME(name) + ' WITH CHECK_POLICY = OFF'

  FROMsys.sql_logins

  WHEREis_policy_checked = 1 AND

  name not LIKE '##%' AND

  name != 'sa'

  OPEN c

  FETCH NEXT FROM c INTO @stmt

  WHILE @@FETCH_STATUS != -1

  BEGIN

  EXEC sp_executeSQL @stmt

  FETCH NEXT FROM c INTO @stmt

  END

  CLOSE c

  DEALLOCATE c

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  September 8, 2011 at 11:07 am

  #1378778

  Another option is to have a DDL trigger fire on the CREATE_LOGIN event to alter the login immediately after it is created.

  This has some valuable insight into what event data is available inside the trigger: http://www.sqlservercentral.com/blogs/brian_kelley/archive/2007/07/09/2648.aspx

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato