Joe O'Connor (2/18/2016)
I did some more experimenting with this, and noted one omission that might be useful - including "is_disabled' in the output. As it doesn't appear to come back when bringing in the Windows users, it might have to be a separate step in the job to update the temp table - like right after the Server_Roles section. I'll be trying to add it myself when I get a chance
Great idea! I will try to figure out how to do it here as well. the one thing that I see that might make it problematic is when the windows login is through a group; we would only report on the disabled condition of the group, and not of the windows account itself.
I figured it out. I ran through the list twice, once for the users, and once to cascade the groups down to the users - which isn't terribly important, you cannot actually disable a windows group within SQL (learned that through this experiment).
Snippet of code to add it is:
-- Get is_disabled into the mix
-- Add column to table
alter table #LoginsList add [is_disabled] int;
is_disabled = p.is_disabled
INNER JOIN (Select name, is_disabled FROM sys.server_principals) p
ON LL.[Account Name] = p.name
-- cascade the permission path values down to the users granted access from that path
SET is_disabled = pp.is_disabled
INNER JOIN (select L2.[Account Name], L2.is_disabled FROM #LoginsList L2) pp
ON LL.[Permission Path] = pp.[Account Name]
then just add the column to the output at the end