October 9, 2009 at 1:22 pm
I had a user ask earlier about some logins that have CHECK_POLICY and CHECK_EXPIRATION on..
Being a curious kinda person, I thought to myself, ha.. maybe I'll write I'll list out which logins have what..
So I started out with sys.syslogins thinking it'd be a simple 0/1 jobby..
But I dont see where these options are in there.. maybe I'm just not seeing them.. it has been a long week..
I took a look through some other system views, but not seeing anything jump out at me..
can anyone tell me where the CHECK_POLICY and CHECK_EXPIRATION flags are for a login?
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 9, 2009 at 1:39 pm
Here's the query that SSMS uses to retrieve that information:
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##')
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2009 at 1:44 pm
wow.. nice thanks..
just out of interest.. how did you get this? sql profiler?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 9, 2009 at 1:48 pm
torpkev (10/9/2009)
wow.. nice thanks..just out of interest.. how did you get this? sql profiler?
Yup. I love profiler (on a dev instance of course). I actually think I have the script kicking around somewhere too, but profiler is just as fast as finding it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2009 at 1:49 pm
to be honest.. should have thought of that myself..
thanks! 🙂
edit: i see my problem now.. i was reading syslogins when i should have been looking at sql_logins.. doh!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply