Send alter login SQL statement to local mssql server.

[Bendo]

Hi all,

First post so go easy...

I'm trying to send a SQL command to the SQL Express loaded locally on my PC, in the hope of doing this to a server SQL once I get the syntax right...

I'm trying to alter the login of a user...

#include <sqlite.au3>
#include <_sql.au3>
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("DRIVER={SQL Server};SERVER=HTCL\SQLEXPRESS;DATABASE=adventureworks;UID=sa;PWD=asdf;")
if @error Then
MsgBox(0, "ERROR", "Failed to connect to the database")
Exit
Else
MsgBox(0, "Success!", "Connection to database successful!")
EndIf
_SQLite_Exec( "$hHTCL\SQLEXPRESS", "$salter login 'user1' with password = '2222' ")

Output is...

"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\1\6.au3"

>Exit code: 0 Time: 1.867

I connect to the db successfully as the popup shows up with successful!.

There are no error messages from the _sqlite_exec line

The user exists but the password is not changed.

I've done the rest of the GUI and logic (left off for clarity)

Happy for any solution that changes the users password.

[hannes08]

Hi Bendo,

you're mixing SQL Express with SQLite, wich are two totally different engines.

Look in the forum for the _sql.udf which has some great functions to work with MS SQL (Express) Server.

[Bendo]

Thanks hannes08...

However this is really puzzling...

this works... hardcoded user and password...

$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB;Data Source=HTCL\SQLEXPRESS;DATABASE=master;User ID=sa;Password=password;")
$sqlCon.Execute("alter login user1 with password ='11'")

however the following does not...

$loginame = "user1"
$newpass = "77"
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB;Data Source=HTCL\SQLEXPRESS;DATABASE=master;User ID=sa;Password=password;")
$sqlCon.Execute("alter login $loginame with password ='$newpass'")

nor any combination of changes I can come up with.

Where the '$newpass' is in the execute line, it will make the password $newpass rather than 77.

It also hates any change where $loginame is put in the command line.

No matter what brackets curly, square, round, or any other delimeters I do.

I've got around it by doing the following... making up the commandline bit by bit and sending that. It works.

$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB;Data Source=HTCL\SQLEXPRESS;DATABASE=master;User ID=sa;Password=password;")
$partcommand1 = "alter login "
$partcommand2 = " with password ='"
$partcommand3 = "'"
$commandline=$partcommand1&$username&$partcommand2&$password&$partcommand3
$sqlCon.Execute($commandline)

Thanks again for the advice on what I was doing wrong.

Hopefully this helps someone else.