--Create table to hold AD UserAccountControl data
CREATE TABLE [dbo].[AD_UserAccountControl](
[Flag] [decimal](20,0) ,
[Details] [varchar](100)
) --Insert Values from http://support.microsoft.com/kb/305144
insert into AD_UserAccountControl values(1,'SCRIPT')
insert into AD_UserAccountControl values(2,'ACCOUNTDISABLE')
insert into AD_UserAccountControl values(8,'HOMEDIR_REQUIRED')
insert into AD_UserAccountControl values(16,'LOCKOUT')
insert into AD_UserAccountControl values(32,'PASSWD_NOTREQD')
insert into AD_UserAccountControl values(64,'PASSWD_CANT_CHANGE')
insert into AD_UserAccountControl values(128,'ENCRYPTED_TEXT_PWD_ALLOWED')
insert into AD_UserAccountControl values(256,'TEMP_DUPLICATE_ACCOUNT')
insert into AD_UserAccountControl values(512,'NORMAL_ACCOUNT')
insert into AD_UserAccountControl values(2048,'INTERDOMAIN_TRUST_ACCOUNT')
insert into AD_UserAccountControl values(4096,'WORKSTATION_TRUST_ACCOUNT')
insert into AD_UserAccountControl values(8192,'SERVER_TRUST_ACCOUNT')
insert into AD_UserAccountControl values(65536,'DONT_EXPIRE_PASSWORD')
insert into AD_UserAccountControl values(131072,'MNS_LOGON_ACCOUNT')
insert into AD_UserAccountControl values(262144,'SMARTCARD_REQUIRED')
insert into AD_UserAccountControl values(524288,'TRUSTED_FOR_DELEGATION')
insert into AD_UserAccountControl values(1048576,'NOT_DELEGATED')
insert into AD_UserAccountControl values(2097152,'USE_DES_KEY_ONLY')
insert into AD_UserAccountControl values(4194304,'DONT_REQ_PREAUTH')
insert into AD_UserAccountControl values(8388608,'PASSWORD_EXPIRED')
insert into AD_UserAccountControl values(16777216,'TRUSTED_TO_AUTH_FOR_DELEGATION')
insert into AD_UserAccountControl values(67108864,'PARTIAL_SECRETS_ACCOUNT')
go -- Create function to call details for UserControlFlag
create FUNCTION [dbo].[fn_AD_UserAccountControlDetails] (@flag decimal(20,0))
RETURNS VARCHAR(max) AS BEGIN
IF @flag IS NULL
BEGIN
RETURN 'UNKNOWN'
END DECLARE @Details varchar(max), @curr varchar(100)
select @Details = ''
while (select @flag) > 0
begin
select @Details = @Details + ' ' + (select top 1 details from [dbo].[AD_UserAccountControl] where flag <= @flag order by flag desc)
select @flag = @flag - (select top 1 flag from [dbo].[AD_UserAccountControl] where flag <= @flag order by flag desc)
end
select @Details = replace(ltrim(rtrim(@Details)),' ',', ')
RETURN @Details
END go
-- How to use the function SELECT [dbo].[fn_AD_UserAccountControlDetails] (514) as TestControlDetails go
-- The function can be used combined with any table that contains UserAccountControl data
SELECT [Flag] ,[Details],[dbo].[fn_AD_UserAccountControlDetails] (flag) as UserStatus
FROM [dbo].[AD_UserAccountControl]
go |
