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

how best to tackle logins with SQL authentication after automatic failover

In an sql authentication environment with an automatic failover in database mirroring how to you manage new logins which have been created on the principle since the start of mirroring? Since the master cannot be mirrored, and the mirror database cannot be read during mirroring (except as a snapshot) in order to find the missing logins, I assume that only after failover a script should run to create the new logins and then run sp_change_users_login . The qestions are:

1) should the script create a new login first and then run sp_change_users_login with option update_one , or should sp_change_users_login using option

Auto_Fix create the missing logins?

2) But what is the password of these users? is it initially NULL , as a consequence of sp_change_users_login? What about the SIDs?

3) Or should we bypass sp_change_users_login altogether and use

CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...as described in http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx

4) What is the event that would trigger this script to run after the aitomatic failover ?

Is there a definitive MIcrosoft agreed apon and recommended method to tackle this?

If you want your application users to connect after failover you better create those logins beforehand @. both the principal and @. mirror server @. the time of configuring mirroring so that they will be available to you but will be mismatched and after failover you can correct them using sp_change_users_login sp.

and for the less important ones you can make use of SSIS to transfer logins frequently so that after failover you can just restore the master syslogins table or master db and fix the mismatch by change users login sp......

|||

Thank you for your anwer. My question was about the new logins created after mirroring had begun. Of course before mirroring started the existing logins were recreated on the mirrorserver beforehand . The question was whether sp_change_users_login should be used with update_one or Auto_Fix or not used at all . The auto fix option creates a login if there isn't one. The update_one option does not .

Assuming rhat SSIS would transfer the new logins from the princiopal master to the mirror master while database mirroring would transfer the users from the principal to the mirror. Will the SIDs be in order? If a script has to be run it would have to be run automatically so what is the trigger that this script will be triggered by?

|||

Actually there are a few issues here:

Firstly SSIS will not help you because as of 2005 the Transfer Logins task does not transfer the passwords, it resets the passwords which to be honest wouldn't help especially if you are using the High Availability option.

Next, you need to take care of the SID's otherwise you will have to sync them. See this KB article

Lastly, In 2005 you cannot create a login which has a default database that is offline. So if an app needs to use Automatic Client Redirect in a High Availability setup and the account is created on the Principal after mirroring has been configured, you will not be able to add the login on the Mirror. The only workaround is to bring the mirror online periodically and sync the logins. I heard it may be fixed in 2008 however; I have not tried yet, maybe someone can clarify for me.