Using SQL to analyze Active Directory UserAccountControl bits to determine if account is DISABLED or ENABLED

0
=
0
+
0
No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.
0

I have a dump of Users data from Active Directory into Oracle table and need to create a view to analyze Active Directory data using SQL. One of the columns in that view needs to be a flag that would contain 'Y' if this user account is disabled in Active Directory, and 'N' if user account is active and user can log in. I have a numeric column called user_account_control that basically contains a value from UserAccountControl AD attribute. I know that there is a bit in that numeric value that tells you if this account is active or not. How should I perform that bitwise operation on a given bit in that field and produce that flag column in Oracle SQL?

1 Answer

1
=
0
=
$0
Internet users could send Bitcoin Tips to you if they like your answer!

Use this SQL code:

select case when bitand(user_account_control, 2) = 0 then 'N' else 'Y' end AS account_disabled
from active_directory_user_table

Here, decimal value 2 used as a parameter to BitAnd function represents the ACCOUNTDISABLE bit that you want to analyze. This SQL expression returns 'N' if account is active (not disabled), and 'Y' if not active (disabled)

SEND BITCOIN TIPS
1

Too many commands? Learning new syntax?

FavScripts.com is a free tool to save your favorite scripts and commands, then quickly find and copy-paste your commands with just few clicks.

Boost your productivity with FavScripts.com!

Post Answer