Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option

CHECK_POLICY = OFF;

After creating a user, I have to run the command

ALTER LOGIN username WITH CHECK_POLICY = OFF;

Disable the policy, as recommended, I can not.

Is it possible to disable the default CHECK_POLICY that without her CREATE LOGIN user created with CHECK_POLICY = OFF?

share|improve this question

migrated from stackoverflow.com May 29 '13 at 13:01

This question came from our site for professional and enthusiast programmers.

2  
Why do you upgrade from an ancient version to another ancient version? We had 3 new versions since your "new" version. –  Marian May 29 '13 at 12:46
2  
I would assume that the answer to that question is because the software vendor only supports up to SQL 2005. It's very common. –  mrdenny May 29 '13 at 17:11
    
To mrdenny: Software vendor does not consider this a problem. –  Arty May 30 '13 at 12:13
add comment

2 Answers 2

up vote 6 down vote accepted

You do it with a DDL Trigger that captures the DDL event for CREATE LOGIN

CREATE TRIGGER PolicyStuff
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON;
IF EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(100)') = 'SQL Login'
BEGIN
    DECLARE @sql nvarchar(1000) = 'ALTER LOGIN ' + QUOTENAME(EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')) + ' WITH CHECK_POLICY = OFF;';
    EXEC(@sql);
END
GO

CREATE LOGIN foo WITH PASSWORD='C-mplex654Password', CHECK_POLICY=ON;
GO
SELECT SL.name, SL.is_policy_checked FROM sys.sql_logins SL WHERE SL.name = 'foo'
GO
DROP LOGIN foo
GO
share|improve this answer
    
Were DDL triggers available in SQL 2005? –  mrdenny May 29 '13 at 17:11
1  
@mrdenny: yes msdn.microsoft.com/en-us/library/ms189799(v=sql.90).aspx –  gbn May 29 '13 at 17:55
    
Cool. It's been a while since I've had to do anything exciting with SQL 2005. Couldn't for the life of me remember. They would have been my suggestion as well. –  mrdenny May 29 '13 at 22:17
    
I'm nowhere in the documentation does not find any mention of what CHECK_POLICY not be turned off by default. But it seems that the way it is. –  Arty May 30 '13 at 12:17
    
@Arty: I looked all over too. Including various MS SQL Server blogs. It can't be set globally from what I read. –  gbn May 30 '13 at 12:51
add comment

these wont work if you have created your login with MUST_CHANGE and user didn't change the default password otherwise its working fine...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.