Can I use ALTER LOGIN in a stored procedure?

Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:

ALTER PROC [dbo].[lbxChangePassword]
(
@.loginid nvarchar(180),
@.oldpassword nvarchar(40),
@.newpassword nvarchar(40)
) AS BEGIN

IF @.oldpassword = (SELECT password FROM contacts WHERE loginid = @.loginid)
BEGIN
BEGIN TRANSACTION
UPDATE contacts
SET password = @.newpassword
WHERE loginid = @.loginid

ALTER LOGIN @.loginid WITH PASSWORD=@.newpassword OLD_PASSWORD=@.oldpassword
END
ELSE
BEGIN
RAISERROR(N'The password you entered does not match your current password.', 16, 1)
RETURN
END

IF @.@.ERROR <> 0
BEGIN
RAISERROR(N'There was an error creating your new password.', 16, 1)
RETURN
END

COMMIT TRANSACTION

END

************
This returns:

Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near '@.loginid'.
Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
************

If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.

I guess you have to put that in dynamic sql to execute:

DECLARE @.SQLStrng NVARCHAR(500)
SET @.SQLString = 'ALTER LOGIN ' + @.loginid + ' WITH PASSWORD= '' + @.newpassword + '' OLD_PASSWORD= '' + @.oldpassword + '''
EXEC(SQLString )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, the DDL does not accept variables. If you build the string dynamically, use quotename() around the variable names to get them quoted properly:

DECLARE @.SQLStrng NVARCHAR(500)
SET @.SQLString = 'ALTER LOGIN ' + quotename(@.loginid) + ' WITH PASSWORD= ' + quotename(@.newpassword, '''') + ' OLD_PASSWORD= ' + quotename(@.oldpassword, '''')
EXEC(SQLString )

Thanks
Laurentiu

|||

I'll try this, but I was hoping that there would be a way to do this without using a "hack".

Am I not approaching this the right way? The user will be logging into a PHP application and I need for them to be able to change their password. Would you recommend a stored procedure to accomplish this like the ones we've been writing about, or is there a best practice for this. I mean, otherwise how can one administer database users from a web app and still use SQL Server's security model? I don't understand Microsoft's reasoning in this architecture.

|||

Writing a stored procedure to accomplish this is OK. If you don't like to build the dynamic SQL and execute it with EXEC, you might prefer using sp_executesql instead: http://msdn2.microsoft.com/en-US/library/ms175170(SQL.90).aspx.

Thanks
Laurentiu

|||

Well, I read a post by a SQL Server MVP that said that another way to do this is to specify the old/new password in a connection string to the SQL Server Native Client. e.g.:

"Provider=SQLNCLI;Data Source=MyServer;User ID=MyLogin;Old Password=oldpassword;Password=password"

But I don't think PHP is friendly to connection strings though; I tried fiddling with ODBC connections in PHP with no luck. Has anyone heard anything about this?

|||

Try posting on SQL Server Data Access for more details on the connection string idea. I think this connection string is probably meant to be used when a login password is expired; it is not the regular way of changing a login password. If you're looking at all possible ways of changing a login password, you should also check the SQL Server SMO/DMO forum - you could write a CLR procedure to perform this action (see .NET Framework inside SQL Server for questions on using CLR inside SQL Server).

Thanks
Laurentiu

|||

I'm just a curious person :-) Thanks. Good Ideas.

-Ryan