January 31, 2012 at 6:25 am
I'm trying to identify which logins are utillizing the 2005 password complexity. Does anyone have a script to do this?
January 31, 2012 at 7:46 am
I use this to turn off password expiration for all users.
You may modify as needed:
DECLARE @sql AS VARCHAR(500)
DECLARE @current_user AS VARCHAR(50)
DECLARE Reset_expiration_cursor CURSOR FOR
SELECT name FROM sys.sql_logins --WHERE is_expiration_checked = 1
OPEN Reset_expiration_cursor
FETCH NEXT FROM Reset_expiration_cursor INTO @current_user
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE [master]
ALTER LOGIN [' + @current_user + '] WITH DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=ON'
EXEC(@sql)
FETCH NEXT FROM Reset_expiration_cursor INTO @current_user
END
CLOSE Reset_expiration_cursor
DEALLOCATE Reset_expiration_cursor
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 31, 2012 at 10:56 am
If you have access to Powershell it's a three liner
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
$srvObj = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'YourServerNameHere'
$srvObj.Logins | Where {$_.LoginType -eq 'SQLLogin'} | Select Name, LoginType, PasswordExpirationEnabled, PasswordPolicyEnforced | Format-Table -AutoSize
February 1, 2012 at 7:08 am
Thanks a million
February 2, 2012 at 9:00 am
If you're doing this for auditing reasons, you might also want to see what happens when you set up an account without the complexity enforced with a password that would not meet the guidelines, and afterwards turn the complexity requirement on.
AFAIK, SQL Server would have no reasonable way of knowing if an existing password met any such requirement or not (nor should it!), though I'm not sure whether it would for a password change when complexity is turned on or not.
February 3, 2012 at 8:56 am
Is this what you are looking for:
SELECT
log.name AS [Name],
ISNULL(log.default_language_name,N'') AS [Language],
l.alias AS [LanguageAlias],
ISNULL(log.default_database_name, N'') AS [DefaultDatabase],
CAST(CASE sp.state WHEN N'D' THEN 1 ELSE 0 END AS bit) AS [DenyWindowsLogin],
CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType],
CASE WHEN (N'U' != log.type AND N'G' != log.type) THEN 99 WHEN (sp.state is null) THEN 0 WHEN (N'G'=sp.state) THEN 1 ELSE 2 END AS [WindowsLoginAccessType],
CAST(CASE WHEN (sp.state is null) THEN 0 ELSE 1 END AS bit) AS [HasAccess],
log.sid AS [Sid],
CAST(sqllog.is_policy_checked AS bit) AS [PasswordPolicyEnforced],
CAST(sqllog.is_expiration_checked AS bit) AS [PasswordExpirationEnabled],
log.create_date AS [CreateDate],
log.modify_date AS [DateLastModified],
CAST(LOGINPROPERTY(log.name, N'IsLocked') AS bit) AS [IsLocked],
CAST(LOGINPROPERTY(log.name, N'IsExpired') AS bit) AS [IsPasswordExpired],
CAST(LOGINPROPERTY(log.name, N'IsMustChange') AS bit) AS [MustChangePassword],
log.principal_id AS [ID],
ISNULL(c.name,N'') AS [Credential],
ISNULL(cert.name,N'') AS [Certificate],
ISNULL(ak.name,N'') AS [AsymmetricKey],
log.is_disabled AS [IsDisabled],
CAST(CASE WHEN log.principal_id < 256 THEN 1 ELSE 0 END AS bit) AS [IsSystemObject]
FROM
sys.server_principals AS log
LEFT OUTER JOIN sys.syslanguages AS l ON l.name = log.default_language_name
LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = log.principal_id and sp.type = N'COSQ'
LEFT OUTER JOIN sys.sql_logins AS sqllog ON sqllog.principal_id = log.principal_id
LEFT OUTER JOIN sys.credentials AS c ON c.credential_id = log.credential_id
LEFT OUTER JOIN master.sys.certificates AS cert ON cert.sid = log.sid
LEFT OUTER JOIN master.sys.asymmetric_keys AS ak ON ak.sid = log.sid
WHERE
(
log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND
log.name <> N'##MS_AgentSigningCertificate##')and(log.name='My_login')
February 3, 2012 at 10:58 am
if you comment out the following words at the last line, you will see some results.
--and(log.name='My_login')
February 3, 2012 at 11:07 am
Jing. (2/3/2012)
if you comment out the following words at the last line, you will see some results.--and(log.name='My_login')
Perfect....i can see now
thanks
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply