Filter "Log-in Enabled" Users From SQL Report

I would like to filter CS users which has the option "Log-in Enabled" down the user privileges on my sql report. I want to include users which has this option checked.

Tried to search at the DAuditNewCore, LoginAndAudit, KUAF tables but without this data field.

Any advices will be welcomed.

Find more posts tagged with

Comments

Appu Nair

These are BITMAPS in the KUAF.PRIVILEGES field ,Computer programs all over the world use this important feature to figure out those things the only problem here is people like you and me should be able to know what Bit is On or off usually using builder one can find it or since this is a very basic thing OT has given that already. If it is SQLserver I think I have this, if you are on Oracle then its corresponding BitAnd methods need to be used if you wanted to find if a user is a System Administrator in livelink what the code does is divides the DECIMAL in the column/16 which is 2^4 if that Bit is ON then he is a SA(search google for math involving 2's complements) 2^0 is Login Enabled.

 select KF.LastName, KF.FirstName, KF.name as [UserID], Department.Name as [Department],
 (CASE WHEN (KF.UserPrivileges/1) % 2 = 1 then 'Yes' else ' ' END) As [Login enabled],
 (CASE WHEN (KF.UserPrivileges/2048) % 2 = 1 then 'Yes' else ' ' END) As [Public Access],
 (CASE WHEN (KF.UserPrivileges/32) % 2 = 1 then 'Yes' else ' ' END) As [Create Users],
 (CASE WHEN (KF.UserPrivileges/64) % 2 = 1 then 'Yes' else ' ' END) As [Create Groups],
 (CASE WHEN (KF.UserPrivileges/16) % 2 = 1 then 'Yes' else ' ' END) As [User Admin],
 (CASE WHEN (KF.UserPrivileges/256) % 2 = 1 then 'Yes' else ' ' END) As [System Admin],
 (CASE WHEN (KF.UserPrivileges/8192) % 2 = 1 then 'Yes' else ' ' END) As [E Discovery Enabled]
 FROM kuaf KF
INNER JOIN kuaf Department ON KF.GroupID = Department.ID  --we will get User's Department (Base Group)
 WHERE KF.Deleted = 0 and KF.Type=0 --only want users and that too non deleted ones
 AND --query is only interetesd in knowing if any of the higher bits are present you can remove the AND section to run the whole table
 ( 
 ((convert(int,(KF.UserPrivileges/32)) % 2)=1)  --only if create users bit is present
 OR
 ((convert(int,(KF.UserPrivileges/64)) % 2)=1)  --or only if create groups bit is present
 OR
 ((convert(int,(KF.UserPrivileges/16)) % 2)=1)   -- or only if UA bit is present
 OR
 ((convert(int,(KF.UserPrivileges/256)) % 2)=1) --or only if SA bit is present 
 )
 
 ORDER BY KF.LastName, KF.FirstName

Shai Abadi

You sir are a legend!