Solved

which logins are locked?

Posted on 2013-10-30
3
271 Views
Last Modified: 2013-10-30
i could see anything like 'islocked' columns in any of the system views?

could you write   a query that will quickly tell if any login is locked out in the sql server instance?

thanks
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 250 total points
ID: 39611851
Please refer to this blog, here is the code sample:
 /*
This script is designed to find the logins those are locked out in the database Server.
It is tested to run on:
SQL 2000 - NO (Login lock feature was not introduced in this SQL version)
SQL 2005 - YES
SQL 2008 - YES
*/
set nocount on
declare  Cur_locked_login cursor for select name from master..syslogins order by name
declare @loginname nvarchar(100)
open Cur_locked_login
fetch next from Cur_locked_login into @loginname
while @@FETCH_STATUS >= 0
begin
if (select LOGINPROPERTY(@loginname,'islocked')) =1
print 'Login -> ' + @loginname +' is LOCKED'
fetch next from Cur_locked_login into @loginname
end
close Cur_locked_login
deallocate Cur_locked_login

Open in new window


http://dbasearch.blogspot.ca/2011/10/sql-login-locked-out-status-check.html
0
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 39611875
Hi 25112 ,

Please check this query.

select * from sys.server_principals
where is_disabled = 1
0
 
LVL 21

Accepted Solution

by:
Tapan Pattanaik earned 250 total points
ID: 39611876
SELECT LOGINPROPERTY('UserName', 'IsLocked')
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question