Orphaned Users

Question

Orphaned Users

Tom Goltl

SSCertifiable

Points: 5755
More actions
March 21, 2008 at 8:28 am

#188372

I have a problem I hope someone has a solution to. We run sp_validatelogins to identify and delete windows accounts that have been deleted. The problem is our network admins will often times just disable the accounts of users leaving us no way of finding orphaned users and having logins for a lot of accounts that will never be used. Is there a way of modifying or creating a procedure to look for disabled accounts as opposed to non existent accounts.
Thanks in advance.
Tom

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 1

I don't know of a way in T-SQL. The problem is that you'd need AD rights to check for the accounts being disabled. Plus if they're disabled, what if they're re-enabled, I think you'd want to keep them around for that reason.

There are two things to do here. One is get the network folks to alter their script so that when the delete an account, it removes the account from SQL right away. This would require you keeping the list of servers up to date. Or having their script notify you.

I'd be wary of why they disable accounts and why you would delete them. The inactive accounts don't affect SQL Server, but be sure you understand the business rules about why you each make your decision.

Matt Cunningham Old Hand Points: 378 More actions · Answer 2

Tom, Standard practice in a lot of places is to disable for 30 days and delete after 60 or 90 days. It sounds like you have the SQL end covered for removing deleted AD accounts. The AD admins have the responsibility to follow the predefined process (whatever that is) to remove unneeded accounts. Don't take it upon yourself to "work around". Have them fix the process if it is broken

_______________________________________________________________________
Work smarter not harder.

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 3

Thanks for the replies. These accounts are disabled and the people are terminated. They don't want to delete them because when a replacement is hired they use the old account as a template. The problem being it could be months/years to delete these users. Because of applications out of my control, alot of these logins/users have associated SQL logins. That is the bigger reason I want to query AD for the disabled users. I can then match the usernames up to sql login names.

Thanks for the help.

Tom

Matt Cunningham Old Hand Points: 378 More actions · Answer 4

Sounds like the AD admins need to take a lesson from you on house cleaning. Definately something to bring up at the next IT meeting.

In the mean time try this:

http://www.microsoft.com/technet/scriptcenter/resources/qanda/may05/hey0512.mspx

it's a good one to file away.

On Error Resume Next

Set objConnection = CreateObject("ADODB.Connection")

Set objCommand = CreateObject("ADODB.Command")

objConnection.Provider = "ADsDSOObject"

objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000

objCommand.CommandText = _

" ;(&(objectCategory=User)" & _

"(userAccountControl:1.2.840.113556.1.4.803:=2));Name;Subtree"

Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

Wscript.Echo objRecordSet.Fields("Name").Value

objRecordSet.MoveNext

Loop

_______________________________________________________________________
Work smarter not harder.