0

I'm slowly but surely perfecting a new user script. As it is, my script will do everything to add a new user to our domain, except one thing. We have an in-house program, webpage front end that connects to a SQL database. Typically I go to the webpage and open up a current user for reference, then add the new user with the same criteria (except name, email and username) as the current user. I was thinking it would be nice to add that into the script so that everything is in one location. And, I figured it would be easier to add the new user through the backend than through the webpage front-end (using PowerShell anyway). I don't actually have much SQL experience but I figured this would be a good time to learn some basic commands while learning PowerShell.

So in summation, I'd like the script to go to the SQL server, look for the user I tell it (I already have a variable for current user for something else), create a new user just like that user with the exception of his/her name/email address/login name. There are a handful of fields that would be the same; department, groups, etc.

Thanks for any pointers you can give!

Don
  • 838
  • 8
  • 19
  • 33
  • 1
    FYI it's easier (and best practice) to use the AD user as the credential for SQL rather than create SQL users – Jim B Apr 27 '12 at 15:10
  • Jim B is right about the best practice. However, since you're talking about setting fields like department and groups, it sounds like you're not trying to create a SQL login but rather a user specific to your application (such as in your application's User table). Is that right? If so, the processes are completely different. – squillman Apr 27 '12 at 16:18

1 Answers1

0

You can use the standard .Net data access objects to execute SQL or stored procedures on your database. I don't know the details of your problem, but your script will probably begin something like this (with a couple of changes if you are using a non-Microsoft database): 

$connStringBuilder = new-object System.Data.SqlClient.SqlConnectionStringBuilder
$connStringBuilder["Data Source"] = "SOME_SERVER"
$connStringBuilder["Initial Catalog"] = "YourDatabase"
$connStringBuilder["User ID"] = "user"
$connStringBuilder["Password"] = "password"

$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $connStringBuilder.ConnectionString

$comm = new-object System.Data.SqlClient.SqlCommand
$comm.CommandText = "INSERT INTO FOOBAR VALUES... "
$comm.CommandType = $CommandType
$comm.Connection = $conn
$rowsAffectedCount = $comm.ExecuteNonQuery()

OK - so that's not a totally realistic example. You'll probably end up writing a couple of functions and maybe using less crude approaches than a simple INSERT. Anyway - that should get you started.

Assuming you can also create stored procedures then this might be a good reference on how to solve the INSERT or UPDATE problem

For the details - you probably want to dig around in the documentation on MSDN.

Dominic Cronin
  • 670
  • 4
  • 21