SQL Tips by Namwar Rizvi

April 28, 2007

How to change user name in ASP.NET 2.0 Membership Provider

Filed under: Uncategorized — namwar @ 12:43 AM

Hi Guys,

Although changing the UserName in ASP.NET 2.0 Membership provider is not recommended and Microsoft has made it read only but sometime it becomes necessary to change the incorrect or misspelled login name or user name. To solve this issue, I have created the following stored procedure which can easily change the old Login Name to New Login Name. I have tested it and it is running perfectly fine. It is in SQL Server 2005 TSQL format.

NOTE: Before executing, replace tblUsers and its respective columns with your own User Table.

Following is the code:
Create Procedure uspUsers_ChangeLoginName
@p_OldName varchar(20),
@p_NewName varchar(20)

as
Begin
Set Nocount On
BEGIN TRY
–Check if old user exists
if exists(Select Login from tblUsers where Lower(Login)=Lower(@p_OldName))
Begin
Begin Transaction
Update tblUsers set Login=@p_NewName where Login=@p_OldName
Update aspnet_Users Set
UserName=@p_NewName,
LoweredUserName=Lower(@p_NewName)
Where LoweredUserName=Lower(@p_OldName)
Commit Transaction
Print @p_OldName + ‘ has been successfuly changed to ‘ + @p_NewName
End
Else
Begin
Print ‘Error: Old User Name “‘+@p_OldName + ‘” does not exist. Cannot change the User Name.’
End
END TRY
BEGIN CATCH
–Rollback any tranction, if it was started
If @@Trancount > 0 Rollback Transaction
Print ‘Error occured while trying to replace old UserName to New UserName:’
Print ‘Error No:’ + Convert(varchar,ERROR_NUMBER())
Print ‘Error Line:’ + Convert(varchar,ERROR_LINE())
Print ‘Error Message:’ + Convert(varchar,ERROR_MESSAGE())
END CATCH;
End

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: