locked
create login: CHECK_POLICY=ON workaround ? RRS feed

  • Question

  • 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 ?

    Sincerely, Lynx K.
    Friday, September 2, 2011 2:33 PM

Answers

  • Hallo Lynx,

    run a script for a predefined period with sql agent which runs the following commands

    DECLARE	@stmt	nvarchar(255)
    
    DECLARE	c CURSOR
    FOR
    	SELECT	'ALTER LOGIN ' + QUOTENAME(name) + ' WITH CHECK_POLICY = OFF'
    	FROM	sys.sql_logins
    	WHERE	is_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
    

    This script will disable POLICY_CHECK for all SQL Logins where this option is activated.

    Get more details about ALTER LOGIN here...
    http://msdn.microsoft.com/en-us/library/ms189828.aspx


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    • Marked as answer by Lynx Kepler Friday, September 2, 2011 3:11 PM
    Friday, September 2, 2011 2:44 PM

All replies

  • Hallo Lynx,

    run a script for a predefined period with sql agent which runs the following commands

    DECLARE	@stmt	nvarchar(255)
    
    DECLARE	c CURSOR
    FOR
    	SELECT	'ALTER LOGIN ' + QUOTENAME(name) + ' WITH CHECK_POLICY = OFF'
    	FROM	sys.sql_logins
    	WHERE	is_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
    

    This script will disable POLICY_CHECK for all SQL Logins where this option is activated.

    Get more details about ALTER LOGIN here...
    http://msdn.microsoft.com/en-us/library/ms189828.aspx


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    • Marked as answer by Lynx Kepler Friday, September 2, 2011 3:11 PM
    Friday, September 2, 2011 2:44 PM
  • Interesting, I was thinking of creating a job to run in an hourly basis doing this:

     

    UPDATE sys.sql_logins
    SET is_policy_checked = 0
    WHERE	name not LIKE '##%' AND
    	name != 'sa'
    
    

    Is there a reason to use ALTER LOGIN instead of updating sys.sql_logins ?

     


    Sincerely, Lynx K.
    Friday, September 2, 2011 2:57 PM
  • Interesting, I was thinking of creating a job to run in an hourly basis doing this:

     

    UPDATE sys.sql_logins
    SET is_policy_checked = 0
    WHERE	name not LIKE '##%' AND
    	name != 'sa'
    
    

    Is there a reason to use ALTER LOGIN instead of updating sys.sql_logins ?

     


    Sincerely, Lynx K.


    Hallo Lynx,

    oh yes - it is. You should avoid to manipulate system relations manually.

    To run changes in system tables you have to use sp_configure to allow ad hoc changes to them.

    Use Master
    go
    sp_configure 'Allow updates',1
    go
    reconfigure with override
    go
    
    

    But - as mentioned - avoid manipulations in system relations by yourself.
    In most cases there may be many other steps which have to processed...

     


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Monday, September 5, 2011 7:04 AM