January 9, 2011 at 10:50 pm
Hi,
Just wondering if there is any query which can give me report of locked account in SQL 2008. I know there are options like:-
1) Can go in ssms--security--account and right click and select status. This is applicable to individual account.
2)SELECT LOGINPROPERTY('accountid', 'IsLocked'). Also applicable to individual account
3) SELECT name,type_desc,is_disabled,modify_date,default_database_name from sys.server_principals order by type_desc
Generate the report but give the informatio as is_disabled. But I think is_disabled <> is_locked.
Please provide your valuable inputs
----------
Ashish
January 10, 2011 at 1:59 am
Generate the report but give the informatio as is_disabled. But I think is_disabled <> is_locked.
Yes, Disabled is different from Locked.
SELECT LOGINPROPERTY('loginname', 'IsLocked') is also my guess.
I think if the denylogin = 1 or hasaccess = 0 then the account is locked.
Run below code to get the sql logins.
select denylogin, hasaccess, status, loginname from sys.syslogins
where isntuser = 0 and isntgroup = 0 and status = 9
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 10, 2011 at 2:13 am
Thanks for your response.
I am not able to see any value which gives the confirmation that account is locked(except status which I am not sure what exact meaning of status = 9 or 10).
Also I have googled about sys.syslogns and found these facts :-
*************************************************
status smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
denylogin int 1 = Login is a Microsoft Windows user or group and has been denied access.
hasaccess int 1 = Login has been granted access to the server.
***************************************************
PS:- The above detail is collected from http://msdn.microsoft.com/en-us/library/ms178593.aspx
----------
Ashish
March 1, 2011 at 5:23 pm
Ive used your suggested code to resolve an issue
Thank you
August 15, 2011 at 12:51 pm
Maybe late, but try this...
SELECT name
,is_disabled
,CAST(LoginProperty(name, 'IsExpired') AS INT) is_expired
,CAST(LoginProperty(name, 'IsLocked') AS INT) is_locked
FROM sys.server_principals
WHERE (is_disabled = 0 AND CAST(LoginProperty(name, 'IsExpired') AS INT) = 1)
OR CAST(LoginProperty(name, 'IsLocked') AS INT) = 1)
ORDER BY name
August 10, 2015 at 7:55 am
Excellent
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply