August 1, 2009 at 12:27 am
Hi friends,
we have more than 56 porduction server sql 2000 and 2005, so i need one urgent requirement for getting sql query 2000 and 2005 Info like below, can anyone give me suggestion.
User name
Creation date and time
Last login date and time
Last password change date and time
Last database activity date and time
Date and time the user was last locked out
Current lockout status
Number of failed password attempts and the beginning date and time of the password attempt window
Number of failed password answer attempts and the beginning date and time of the password answer attempt window
Thanks advance
August 3, 2009 at 9:13 am
Some of those items are not available in 2000 (lockout, password change, etc...).
In SQL Server 2005/2008 you should start with sys.server_principals. This will give name, create_date, and modify_date.
Here's the query SSMS uses on 2005 when you bring up a login:
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 = @_msparam_0)
I got that using Profiler. I don't have a 2000 instance to play with right now, but you could do the same thing against a 2000 instance to find the code it uses.
I think you would need a server-side trace to get the rest of the information you want, but I'm not sure.
In 2005/2008 the default trace does audit logins. Check out this script.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply