Jump to content
Sign in to follow this  
Bendo

Send alter login SQL statement to local mssql server.

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×