December 3, 2018 at 12:51 pm
We have a project where I need to (1) uncheck the enforce password policy for all SQL logins on ALL servers (2) change the max length to the local windows policy (3) checkbox the enforce password policy on all servers.
This is the dynamic SQL which generates the code to uncheck to box:
SELECT N'ALTER LOGIN ' + QUOTENAME(name) + N' WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' FROM sys.sql_logins WHERE is_disabled <>
1
/*
ALTER LOGIN [sa] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER LOGIN [test] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER LOGIN [ksDeleteMe] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER LOGIN [deleteme] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
*/
I have tried this (and using a table varialbe) but couldn't get either to work:
DECLARE
@sql1 NVARCHAR(MAX), @sql2 NVARCHAR(MAX);
SET @sql1 = 'SELECT N''ALTER LOGIN '' + QUOTENAME(name) + N'' WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'' FROM sys.sql_logins WHERE is_disabled <>1 ;'
exec(@sql1)
SET @sql2 = 'EXEC (@sql1)'
exec(@sql2)
December 3, 2018 at 1:23 pm
This sounds like a job for PowerShell
https://community.spiceworks.com/topic/468653-powershell-script-to-remove-the-password-never-expires-in-ad-to-users-in-a-file
December 3, 2018 at 2:01 pm
this generates the sql and potentially kicks off the command:
what would setting the local password length do? isn't it overwritten by the domain password policy anyway? i think that is part of the GPO or AD settings, right?
here's a powershell i use to get that kind of infoImport-Module activedirectory
function Get-DomainPasswordPolicy
{
$domain = [ADSI]"WinNT://$env:userdomain"
$Name = @{Name="DomainName";Expression={$_.Name}}
$MinPassLen = @{Name="Minimum Password Length (Chars)";Expression={$_.MinPasswordLength}}
$MinPassAge = @{Name="Minimum Password Age (Days)";Expression={$_.MinPasswordAge.value/86400}}
$MaxPassAge = @{Name="Maximum Password Age (Days)";Expression={$_.MaxPasswordAge.value/86400}}
$PassHistory = @{Name="Enforce Password History (Passwords remembered)";Expression={$_.PasswordHistoryLength}}
$AcctLockoutThreshold = @{Name="Account Lockout Threshold (Invalid logon attempts)";Expression={$_.MaxBadPasswordsAllowed}}
$AcctLockoutDuration = @{Name="Account Lockout Duration (Minutes)";Expression={if ($_.AutoUnlockInterval.value -eq -1) {'Account is locked out until administrator unlocks it.'} else {$_.AutoUnlockInterval.value/60}}}
$ResetAcctLockoutCounter = @{Name="Reset Account Lockout Counter After (Minutes)";Expression={$_.LockoutObservationInterval.value/60}}
$domain | Select-Object $Name,$MinPassLen,$MinPassAge,$MaxPassAge,$PassHistory,$AcctLockoutThreshold,$AcctLockoutDuration,$ResetAcctLockoutCounter
}
Get-DomainPasswordPolicy |Out-Gridview
DECLARE @AllCommands varchar(max)='';
SELECT @AllCommands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + 'ALTER LOGIN ' + QUOTENAME(name) + ' WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;' +CHAR(13) + CHAR(10)
FROM sys.sql_logins
WHERE is_disabled =0
ORDER BY name
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
print @AllCommands;
--EXECUTE(@AllCommands)
Lowell
December 4, 2018 at 6:31 am
pietlinden - Monday, December 3, 2018 1:23 PMThis sounds like a job for PowerShell
https://community.spiceworks.com/topic/468653-powershell-script-to-remove-the-password-never-expires-in-ad-to-users-in-a-file
I am talking about SQL logins, not domain users accounts.
-Kevin
December 4, 2018 at 9:06 am
Lowell - Monday, December 3, 2018 2:01 PMthis generates the sql and potentially kicks off the command:
what would setting the local password length do? isn't it overwritten by the domain password policy anyway? i think that is part of the GPO or AD settings, right?
here's a powershell i use to get that kind of infoImport-Module activedirectoryfunction Get-DomainPasswordPolicy
{
$domain = [ADSI]"WinNT://$env:userdomain"
$Name = @{Name="DomainName";Expression={$_.Name}}
$MinPassLen = @{Name="Minimum Password Length (Chars)";Expression={$_.MinPasswordLength}}
$MinPassAge = @{Name="Minimum Password Age (Days)";Expression={$_.MinPasswordAge.value/86400}}
$MaxPassAge = @{Name="Maximum Password Age (Days)";Expression={$_.MaxPasswordAge.value/86400}}
$PassHistory = @{Name="Enforce Password History (Passwords remembered)";Expression={$_.PasswordHistoryLength}}
$AcctLockoutThreshold = @{Name="Account Lockout Threshold (Invalid logon attempts)";Expression={$_.MaxBadPasswordsAllowed}}
$AcctLockoutDuration = @{Name="Account Lockout Duration (Minutes)";Expression={if ($_.AutoUnlockInterval.value -eq -1) {'Account is locked out until administrator unlocks it.'} else {$_.AutoUnlockInterval.value/60}}}
$ResetAcctLockoutCounter = @{Name="Reset Account Lockout Counter After (Minutes)";Expression={$_.LockoutObservationInterval.value/60}}
$domain | Select-Object $Name,$MinPassLen,$MinPassAge,$MaxPassAge,$PassHistory,$AcctLockoutThreshold,$AcctLockoutDuration,$ResetAcctLockoutCounter
}Get-DomainPasswordPolicy |Out-Gridview
DECLARE @AllCommands varchar(max)='';SELECT @AllCommands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + 'ALTER LOGIN ' + QUOTENAME(name) + ' WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;' +CHAR(13) + CHAR(10)
FROM sys.sql_logins
WHERE is_disabled =0
ORDER BY name
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) sprint @AllCommands;
--EXECUTE(@AllCommands)
Your code works perfectly. It seems to work because all the commans are one 1 line (very clever) but I don't really follow all the code. Would you mind explaining it?
December 4, 2018 at 3:56 pm
the data can have none to many rows in sys.logins. the statement is just building the commands dynamically, really.
remember you can run multiple commands, and the recommendation is to use the semicolon to separate multiple commands.
the semi colon is still optional(for now), and whitespace, or Carriage Return line feed can be interpreted to separate multiple commands instead.
In my case, what I was doing, is creating a single string, using a neat trick that distinct + FOR XML can do for you to concatenate a whole bunch of strings together.
The CrLf/Char13/10 was actually just for readability.
the string that is built has a initial space, which is a legacy of making comma delimited value. to remove that, the STUFF removes that first character.
another example of FOR XML, where I get table names, plus column names as a single , quote-named list of columns ,might shed more light.
SQL2016? has the new CONCAT function , but the FOR XML technique works for all versions back to at least SQL2005, I believe.
SELECT DISTINCT
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply