slopfront
preferences

Creating new users from SQL server changes?

9 points · submitted Feb 2017 by u/0fsysadminwork

Maybe I am looking at this the wrong way, not having much luck finding an answer.

Currently HR inputs new users/terminations into their excel document, and manually email out the changes. I can then create a new user if I see it, or wait for the helpdesk ticket to come in. I am hoping we can start automating this by creating a sql database, with some sort of input form for them to use. Have a SQL job scheduled to run daily at 6PM, email out changes to the Personnel Action Notification distribution group and if there are hires with the title "security guard" I'd want it to launch my newsecurityguard.ps1 passing the first name, last name, and badge number fields to $firstname, $lastname and $badgenumber variables.

Would like to build on that as well, so if there is a new hire with the title of Call Center Agent, it kicks off the callcenteragent.ps1 script, etc.

Is this the way you would do it?

7 comments · 100% upvoted · other discussions

comments

pattymcfly 5 points · Feb 2017

You are basically defining an identity manager, of which there are many of the shelf solutions available on the market.

jheinikel 2 points · Feb 2017

You could do it with SQL, an HTML front-end, etc. I have a report that is automatically generated from our HR system when someone new is hired. There is an attached CSV and I'm using Outlook automation to get that CSV, create users from it, log, and delete the email.

For your exact scenario, are the scripts that much different? If not, you could have one script and call additional functions depending on the job title. Or a script that calls another script, which has parameter binding, and passes relevant parameters to that script. Its all in how complex you want it to be.

RedditRo55 2 points · Feb 2017

How big is your company? It sounds like they could do with some actual HR software.

If that HR software uses a SQL Server database, you can query that database with Invoke-SqlCmd, and create a new user based off the returned results.

KevMar 2 points · Feb 2017 · Community Blogger

Have you seen the HR spreadsheet? There is a small chance you could import directly in from that. You have two projects here.

The first is collecting user input and saving it into the database. Think web form or Winforms GUI. The second is comparing the SQL database to the AD users, then running a specific script on any users that differ.

pattymcfly 1 points · Feb 2017

You are basically defining an identity manager, of which there are many of the shelf solutions available on the market.

creamersrealm 2 points · Feb 2017

This so many times, we built our own and it has grown into a absolute monster of a product. Now we are trying to trim down what it is used for.

The best thing (sarcasm) about it is that is pretty much reloads it's entire database daily. So your IAM product is unavailable for a few hours a day.

[deleted] 1 points · Feb 2017

[deleted]

creamersrealm 2 points · Feb 2017

It's far more complicated than that. In our case we're 6K+ admin employees and 25K contractors. With employment records stored in different companies in Lawson. With the contractors stored in a completely different database.

So technically you could union all these records together and go with your approach. But you will have exceptions to the rule because they are employed but they don't need a AD account and etc.

Trust me 2 years ago I would have said the same exact thing that you did, "Is it really that hard to create and disable people?'