[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

SQL User list to include status

I am looking to generate a list of all users in SQL that will include the SQL login name plus their current status(i.e. whether their SQL account is locked or not).
0
rwheeler23
Asked:
rwheeler23
  • 2
2 Solutions
 
Phillip BurtonCommented:
select [name], is_disabled from sys.server_principals
where type_desc = 'SQL_LOGIN'

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try this query:
SELECT loginname, CASE LOGINPROPERTY(loginname,'IsLocked') 
						WHEN 0 THEN 'No'
						WHEN 1 THEN 'Yes'
						ELSE 'Domain User'
					END IsLocked
FROM sys.syslogins
WHERE hasaccess=1

Open in new window

0
 
rwheeler23Author Commented:
Thank you gentlemen, one script shows disabled logins and the other shows locked logins.
0
 
rwheeler23Author Commented:
Thank you.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now