SQL Users and Password Policies

Question

SQL Users and Password Policies

phani.1986

Newbie

Points: 7
More actions
February 15, 2016 at 8:39 am

#308049

Hi Team,
There is an audit requirement to extract all the users and their corresponding password policy. I used the below query however, I am not getting the password policy used. I would need help to get the same.
SELECT [name] AS [SQL_User]
,[is_policy_checked] as [IsPwdPolicyEnforced]
,[is_expiration_checked] as [IsPwdExpirationEnforced]
,LOGINPROPERTY([name], 'IsExpired') AS [IsAccountExpired]
,LOGINPROPERTY([name], 'IsLocked') AS [IsAccountLocked]
,LOGINPROPERTY([name], 'IsMustChange') AS [IsMustChange]
,LOGINPROPERTY([name], 'PasswordLastSetTime') AS [PasswordLastResetDT]
,LOGINPROPERTY([name], 'BadPasswordCount') AS [BadPasswordCount]
,LOGINPROPERTY([name], 'DefaultDatabase') AS [DefaultDB]
FROM [sys].[sql_logins]
Thanks,
V S Phanindra Kumar P

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What details of the password policy are you looking for? As far as I know, the password policy is inherited from Windows and doesn't change from login to login.

John

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

If the login has Policy on, then SQL uses the password policy from Active Directory, from the domain if the SQL instance is installed on a domain member, from the local machine if not.

SQL doesn't have options for password complexity, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323547 More actions · Answer 3

I've used the answer below to get the Active Directory password policy via powershell:

https://blogs.technet.microsoft.com/heyscriptingguy/2014/01/09/use-powershell-to-get-account-lockout-and-password-policy/

import-module ActiveDirectory

Get-ADDefaultDomainPasswordPolicy

PS H:\> Get-ADDefaultDomainPasswordPolicy
ComplexityEnabled : True
DistinguishedName : DC=MyDomain,DC=Local
LockoutDuration : 00:00:00
LockoutObservationWindow : 00:30:00
LockoutThreshold : 5
MaxPasswordAge : 90.00:00:00
MinPasswordAge : 00:00:00
MinPasswordLength : 7
objectClass : {domainDNS}
objectGuid : 97caa544-8b63-423d-a3d3-ad363791b77f
PasswordHistoryCount : 7
ReversibleEncryptionEnabled : False

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!