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.


thrillingryanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

csachdevaCommented:
Refer the following link for the sokution of your query

http://msdn2.microsoft.com/en-us/library/ms189540(SQL.90).aspx

regards,
Chetan Sachdeva
0
thrillingryanAuthor Commented:
Chetan, I don't see how event notifications are applicable to what I am trying to accomplish;  your advise is very unclear so far.
0
thrillingryanAuthor Commented:
Eesh, my questions must be getting better lately, because less of them are getting answered grrr... oh well.  I figured it out on my own AGAIN.  One is not able to use ALTER LOGIN from stored procedures without hacking it.  Hopefully, you can get to the following link on the MSDN forums to view the discussion of solutions: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=370877&SiteID=1

I basically ended up having to use the EXEC statement using a dynamic query like:

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

But then say you successfully create the login and user (CREATE USER and CREATE LOGIN work with EXEC and dynamic T-SQL as per the above example)... then you'll probably want to assign roles to the database user and well... I've found no way to add roles to a database using T-SQL in a procedure yet.  I'm guessing this will require a UDF ro CLR stored procedure...  probably using SMO.  grrr... why can't they just make this stuff easy for us? The SQL Server 2005 security model seems very inconvenient to implement correctly if you're not using Windows NTLM authentication; what I'm seeing here is that unless I'm storing credentials in the database like I can do with with any other DBMS I can't implement the functionality to add new users to a system using a website.  

Now this is using the initial release of SQL Server 2005; I haven't installed SP1 yet, but I doubt it's addressed in SP1 either considering that after all the reading I've done about this it just isn't mentioned much.
0
GranModCommented:
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.