How can a user change password in SQL2005 via TSQL?

I have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;

The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;

"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"

TOM will be setup with db roles as well

When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"

At this point I get an error:

RunTime error -2147217900 (80040e14)

ODBC SQL Server Driver][SQL Server] Cannot alter

the login 'TOM', becuase it does not exist or you do

not have permission.

Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?

Thanks in advance for your help.

CH

A user does not need any permissions to change his password, but he cannot change his password policy setting - that option is only settable by someone that has ALTER ANY LOGIN permission. See http://msdn2.microsoft.com/en-us/library/ms189828.aspx.

Thanks

Laurentiu

|||

If I have the users change password statement read; it works.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx' "

However, I am unable to test this on a Win2003 Server right now, so I wonder if the Check Policy will be enforced for user TOM.

Under no circumstances do I want to have Policy Checked since the front end of my app provides the password security.

Any ideas if this is possible?

CH

|||If you are using ADO.NET 2.0 you can also use the

SqlConnection.ChangePassword(ConnectionString, "MyNewSecretpassword");

For changing the password.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the reply.

The problem we have run into is the Windows security, which is set across the domain, maybe set differently than our app. So if the user of our app chooses a different policy that that of the domain (which is fine), the user cannot alter his login since he does not have Alter Any login rights. And we wouldn't want to give him any.

Do you see a way around this?

All I can think of now is to change the password via the Admin login through a separate connection to the database. Something like this;

Dim gConnTmp As New ADODB.Connection
Dim sConnect As String

With gConnTmp
sConnect = "DSN=" & SQLDataSource & ";"
sConnect = sConnect & "UID= 'ADMIN' ;"
sConnect = sConnect & "PWD=" & gsPassword & ";"
.Open sConnect
End With

'change the password here... ALTER LOGIN etc.

'kill connection

gConnTmp.Close
Set gConnTmp = Nothing

Do you see any issues with doing this? I think it looks ok.

CH

|||

If you want to enforce a custom password policy, then you should just create the login with CHECK_POLICY set to off; otherwise, the Windows password policy will be enforced for a password change (on Windows 2003).

Thanks

Laurentiu