This error seems to happen fairly often on this release (3.0.13) and is related to getting the users in the aspnet_* tables out of sync with the users in the DNN Users table
This can happen if you try to add a user but it fails because of constraints like password length. This should roll back better in the next release but if you find yourself in this situation and wan't to try and fix it then the following SQL may help. Warning:
Use this SQL at your own risk and after backing up your DB, I have ran it successfully more than once, but you never know.
First run this to see if the tables are out of Sync:
select * from aspnet_Users au left outer join Users u on au.UserName = U.UserName where U.UserName is null
If that returns records then you can remove them by running this:
DECLARE @UserName varchar (50)
--get a cursor to hold all the orphaned users
--that are in aspnet_Users table that are not in the DNN Users table
DECLARE users_cursor CURSOR FOR
SELECT au.UserName FROM aspnet_Users au
LEFT OUTER JOIN Users u on au.UserName = U.UserName
WHERE U.UserName is null
OPEN users_cursor
-- Perform the first fetch.
FETCH NEXT FROM users_cursor INTO @UserName
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--delete the user from all the aspnet_* tables that it may be in
--one at a time to avoid referrential integrity constraints
delete aspnet_Membership where UserId =
(select am.UserId from aspnet_Membership am inner join aspnet_Users au on am.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_Profile where UserId =
(select ap.UserId from aspnet_Profile ap inner join aspnet_Users au on ap.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_UsersInRoles where UserId
in (select uir.UserId from aspnet_UsersInRoles uir inner join aspnet_Users au on uir.UserId = au.UserId
where au.Username =@UserName)
delete from aspnet_Users where Username =@UserName
I used Wallew's module to import users from a customized DNN2 version. It choked at some usernames that had unusual characters. So the problem showed up and I did the cleaning manually. Basically what needs to be done is to remove the users from the aspnet
prefixed tables: Profile, users, membership. I thought about creating the script but there were only a few users so I just put that into the back burner. Thanks JMitch. one less thing to do.
Does it mean that you are deleting your users registration?. If I roll back to DNN 3.12 are the problem resolved. I have clients that I have tried to register but erro shows user invalid
When a users is removed from a portal, the name is still on the database, and a user with the same name can't be created. Although the name is in the database, nothing can be seen from the users menu of the portal.
Any idea how this can be solved or, at least, how that users from the database can be cleaned?
When a users is removed from a portal, the name is still on the database, and a user with the same name can't be created. Although the name is in the database, nothing can be seen from the users menu of the portal.
Are you saying that you removed the user through the DNN interface and it didn't get totally removed?
That would be a bug that I haven't heard of, but the above script should help you there unless it is a case of the user not getting removed from the DNN Users table, then you would just need to go in and delete the user from that table.
J7Mitch
Star
13145 Points
2632 Posts
User Accounts is currently unavailable
May 13, 2005 04:02 AM|LINK
This error seems to happen fairly often on this release (3.0.13) and is related to getting the users in the aspnet_* tables out of sync with the users in the DNN Users table
This can happen if you try to add a user but it fails because of constraints like password length. This should roll back better in the next release but if you find yourself in this situation and wan't to try and fix it then the following SQL may help. Warning: Use this SQL at your own risk and after backing up your DB, I have ran it successfully more than once, but you never know.
First run this to see if the tables are out of Sync:
select * from aspnet_Users au left outer join Users u on au.UserName = U.UserName where U.UserName is null
If that returns records then you can remove them by running this:
DECLARE @UserName varchar (50)
--get a cursor to hold all the orphaned users
--that are in aspnet_Users table that are not in the DNN Users table
DECLARE users_cursor CURSOR FOR
SELECT au.UserName FROM aspnet_Users au
LEFT OUTER JOIN Users u on au.UserName = U.UserName
WHERE U.UserName is null
OPEN users_cursor
-- Perform the first fetch.
FETCH NEXT FROM users_cursor INTO @UserName
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--delete the user from all the aspnet_* tables that it may be in
--one at a time to avoid referrential integrity constraints
delete aspnet_Membership where UserId =
(select am.UserId from aspnet_Membership am inner join aspnet_Users au on am.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_Profile where UserId =
(select ap.UserId from aspnet_Profile ap inner join aspnet_Users au on ap.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_UsersInRoles where UserId
in (select uir.UserId from aspnet_UsersInRoles uir inner join aspnet_Users au on uir.UserId = au.UserId
where au.Username =@UserName)
delete from aspnet_Users where Username =@UserName
FETCH NEXT FROM users_cursor INTO @UserName
END
CLOSE users_cursor
DEALLOCATE users_cursor
GO
mrswoop
Star
11177 Points
2240 Posts
Re: User Accounts is currently unavailable
May 13, 2005 04:21 AM|LINK
Director of Community Relations, DotNetNuke
It is only with the heart that one can see rightly... what is essential is invisible to the eye.
~ Antoine de Saint-Exupéry
Nocturnal
Contributor
2850 Points
570 Posts
Re: User Accounts is currently unavailable
May 13, 2005 07:09 AM|LINK
GravityPoint for all other group portal hosting
TungstenTech: DNN Site
mcalder
Member
140 Points
28 Posts
Re: User Accounts is currently unavailable
May 13, 2005 03:01 PM|LINK
Thanks John, I ran this on my portal and successfully removed 3 user records. Worked fine.
J7Mitch
Star
13145 Points
2632 Posts
Re: User Accounts is currently unavailable
May 13, 2005 06:34 PM|LINK
hooligannes9...
All-Star
16504 Points
2917 Posts
Re: User Accounts is currently unavailable
May 14, 2005 02:05 AM|LINK
zohra
Member
60 Points
12 Posts
Re: User Accounts is currently unavailable
May 14, 2005 06:49 PM|LINK
J7Mitch
Star
13145 Points
2632 Posts
Re: User Accounts is currently unavailable
May 14, 2005 07:28 PM|LINK
It will only delete users that are not fully registered with DNN. All users that are fully registered and working will be left alone.
You can run the first select query to see what users will be deleted before running the second TSQL script.
If you are using the Membership tables to for other applications besides DNN then you don't want to run this.
I guess it would be easy enough to turn this around and add the members from the aspnet_ tables to the DNN tables.
davidllamas
Member
20 Points
4 Posts
Re: User Accounts is currently unavailable
May 15, 2005 12:27 PM|LINK
When a users is removed from a portal, the name is still on the database, and a user with the same name can't be created. Although the name is in the database, nothing can be seen from the users menu of the portal.
Any idea how this can be solved or, at least, how that users from the database can be cleaned?
Rgrds
J7Mitch
Star
13145 Points
2632 Posts
Re: User Accounts is currently unavailable
May 15, 2005 02:25 PM|LINK
When a users is removed from a portal, the name is still on the database, and a user with the same name can't be created. Although the name is in the database, nothing can be seen from the users menu of the portal.
Are you saying that you removed the user through the DNN interface and it didn't get totally removed?
That would be a bug that I haven't heard of, but the above script should help you there unless it is a case of the user not getting removed from the DNN Users table, then you would just need to go in and delete the user from that table.