Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
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 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.