Can I use ALTER LOGIN in a stored procedure???

Posted on 2006-04-26
Last Modified: 2010-08-05
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)

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

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

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



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.

Question by:thrillingryan
    LVL 4

    Expert Comment

    Refer the following link for the sokution of your query

    Chetan Sachdeva

    Author Comment

    Chetan, I don't see how event notifications are applicable to what I am trying to accomplish;  your advise is very unclear so far.

    Author Comment

    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:

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

    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.

    Accepted Solution

    Closed, 500 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now