• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 749
  • Last Modified:

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.


0
thrillingryan
Asked:
thrillingryan
  • 2
1 Solution
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now