DotNetNuke

Re: User Accounts is currently unavailable

J7Mitch — Fri, 06 Jan 2006 18:56:32 GMT

Try clicking on the checkbox that says "Run as Script", that may help if you are getting an error when trying to run it in the Host>SQL window.

Re: User Accounts is currently unavailable

remc0 — Fri, 06 Jan 2006 17:31:18 GMT

Hi Tim,

I also have troubles with it.

I think it's a major bug.

It appeared when I upgraded to 4.0

I don't know much about SQL, but when I run the query through the sql manager it produced the same error.

When I run the query through the Query Analyzer it went OK.

I don't know why this is, but maybe you could try it.

With regards,

Remco.

Re: User Accounts is currently unavailable

jeewun — Fri, 06 Jan 2006 06:24:04 GMT

Hey everyone,

Hmmm, okay so it sounds like I'm having the same problem as everyone else here. I got the following error when clicking on Admin --> User Accounts on a DNN 3.1.0 install....

DotNetNuke.Services.Exceptions.ModuleLoadException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at _ASP.Users_ascx.__DataBind__control19(Object sender, EventArgs e) at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.WebControls.DataGrid.CreateItem(Int32 itemIndex, Int32 dataSourceIndex, ListItemType itemType, Boolean dataBind, Object dataItem, DataGridColumn[] columns, TableRowCollection rows, PagedDataSource pagedDataSource) at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) at System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.BaseDataList.DataBind() at DotNetNuke.Modules.Admin.Users.UserAccounts.BindData(String SearchText, String SearchField) at DotNetNuke.Modules.Admin.Users.UserAccounts.Page_Load(Object sender, EventArgs e) --- End of inner exception stack trace ---

I tried doing the running the SQL suggested...

select * from aspnet_Users au left outer join Users u on au.UserName = U.UserName where U.UserName is null

By going to Host --> SQL paste into box and click "Execute".

I indeed get 4 records returned.

So, I tried pasting the rest of the SQL in Host --> SQL and clicked "Execute".

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

What I got was the following....

There is an error in your query!

I don't know if there is an error in my query or not, I'm just copying and pasting what's above in Blue...I'm not an SQL wizard by any means. Any suggestions anyone?

Many thanks!

-Tim

Re: User Accounts is currently unavailable

rfreese — Fri, 09 Dec 2005 20:02:14 GMT

Just wanted to add my thanks for this proc - I managed to get a more or less clean install of 3.2 into this unsync condition and (with a little doctoring) this script saved me from starting clean again.

I admit to fiddling with the "is_superuser" flag while trying to hack my way out of some other trouble, so I don't know if I can duplicate it.

Roberta

J7Mitch wrote:

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

Re: User Accounts is currently unavailable

Priest_Merchant — Fri, 25 Nov 2005 21:50:08 GMT

Thanks Mitch,

I started getting the error after a new user tried to register. I used your solution (running the first part to identify which user(s) caused the issue) and it worked great. I then went back in and registered (as a test) and everything worked just fine. Thanks again.

[Error: User Accounts is currently unavailable.
DotNetNuke.Services.Exceptions.ModuleLoadException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at _ASP.Users_ascx.__DataBind__control19(Object sender, EventArgs e) at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.WebControls.DataGrid.CreateItem(Int32 itemIndex, Int32 dataSourceIndex, ListItemType itemType, Boolean dataBind, Object dataItem, DataGridColumn[] columns, TableRowCollection rows, PagedDataSource pagedDataSource) at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) at System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.BaseDataList.DataBind() at DotNetNuke.Modules.Admin.Users.UserAccounts.BindData(String SearchText, String SearchField) at DotNetNuke.Modules.Admin.Users.UserAccounts.Page_Load(Object sender, EventArgs e) --- End of inner exception stack trace ---]

Re: User Accounts is currently unavailable

razorphreak — Tue, 08 Nov 2005 18:34:19 GMT

then you'll have to remove them manually. odd the script didn't help as it should have. you might have some usernames that are really messed up and not entered in properly. You'll have to remove it one at a time and remove the FK constraints before you can remove the actual user record (typically the profile entry, the role entry, user in role entry, and then the user entry).

Re: User Accounts is currently unavailable

DanaNield — Tue, 08 Nov 2005 18:10:55 GMT

Thanks again, but no joy on that updated statement. The number of records in the tables didn't change.

Found one name out of sync but when I attempted to delete it from the aspnet_users table after deleting it from the other tables, I got an FK constraint error...

Re: User Accounts is currently unavailable

razorphreak — Tue, 08 Nov 2005 16:46:17 GMT

The problem you are describing along with the error screen is still a matter of duplicates in the membership tables that do not exist in the Users table. If a username is part of multiple portals, it will appear more than once in the membership tables as well. After running the SQL script to clear out the duplicates, you'll have to look up usernames that exist in membership table and not in the Users table and manually remove them. Typically that means removing the profile entry, the roles entries, and then the user entry itself.

When I looked at this again, the deletes in the SQL script can be changed to the following and should remove all duplicate entries:

delete aspnet_Membership where UserId IN

(select distinct 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 IN

(select distinct 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 distinct uir.UserId from aspnet_UsersInRoles uir inner join aspnet_Users au on uir.UserId = au.UserId

where au.Username =@UserName)

Try that and see what happens.

Re: User Accounts is currently unavailable

DanaNield — Tue, 08 Nov 2005 15:40:55 GMT

Thanks for the help so far, but I'm still out of luck.

More details:

When I attempt to view more than ten members in user accounts I get the following error:

11/8/2005 7:37:57 AM Page Load Exception host AssemblyVersion: 03.01.01; Method: ASP.Users_ascx.__Data ...

AssemblyVersion: 03.01.01
Method: ASP.Users_ascx.__DataBind__control19
FileName:
FileLineNumber: 0
FileColumnNumber: 0
PortalID: 0
PortalName: AMPS
UserID: 2
UserName: host
ActiveTabID: 11
ActiveTabName: User Accounts
AbsoluteURL: /dnn/Default.aspx
AbsoluteURLReferrer: http://www.amps-armor.org/dnn/Admin/UserAccounts/tabid/11/Default.aspx
ExceptionGUID: a4b8a400-fb20-47ca-8654-5c4f1d2f1c00
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
InnerException: Object reference not set to an instance of an object.
Message: DotNetNuke.Services.Exceptions.PageLoadException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at ASP.Users_ascx.__DataBind__control19(Object sender, EventArgs e) at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.WebControls.DataGrid.CreateItem(Int32 itemIndex, Int32 dataSourceIndex, ListItemType itemType, Boolean dataBind, Object dataItem, DataGridColumn[] columns, TableRowCollection rows, PagedDataSource pagedDataSource) at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) at System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.BaseDataList.DataBind() at DotNetNuke.Modules.Admin.Users.UserAccounts.BindData(String SearchText, String SearchField) at DotNetNuke.Modules.Admin.Users.UserAccounts.ddlRecordsPerPage_SelectedIndexChanged(Object sender, EventArgs e) at System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) at System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() at System.Web.UI.Page.RaiseChangedEvents() at System.Web.UI.Page.ProcessRequestMain() --- End of inner exception stack trace ---
StackTrace:
Source:
Server Name: DEDI421

I checked the tables and there are 609 rows in the aspnet_user and 388 in the User. There are also duplicates in the aspnet_user tables. This can't be right.

Dana

Re: User Accounts is currently unavailable

razorphreak — Tue, 01 Nov 2005 16:40:07 GMT

It goes back to the SQL script in this thread. That will remove the out of sync users (users that exist in membership tables but not in the older DNN ones). That's what will clean out the membership tables but to prevent it there are a few issues you got to be aware of:

check for that password field in the Users table; if you still have it there and its still requiring a non-null value, you'll have to update the table manually to allow a null for the password.
there seems to be a bug in which if you delete a user, you delete them from the Users table which creates a out of sync senario. I'm still waiting on a fixed or not in 3.1.1 though.
there also seems to be an issue if you remove a portal, all users are hence deleted from the Users table again. Again not sure if it was fixed in 3.1.1.

Re: User Accounts is currently unavailable

DanaNield — Tue, 01 Nov 2005 15:57:37 GMT

Thanks for that.

I checked the aspnet_membership table and found that the unauthorized users that I deleted (Through a 3rd party module, not through DNN's user accounts module, my bad!) were still showing up.

As my knowledge of the aspnet_ tables isn't that great, could someone provide an example script that would bring balance to these tables? I've backed up my DB but I'm fearful of making the dogs breakfast out of it.

Dana

Re: User Accounts is currently unavailable

razorphreak — Tue, 01 Nov 2005 14:37:45 GMT

I too have had this issue. The problem revolves around the membership tables. What you have to accomplish is after you've sync'ed the users, the invalid usernames you'll have to completely remove from the membership tables as well, not just the old Users table.

Be aware also that what I would suggest doing is in the Users table, mark the Password field to allow a NULL value - reason behind this is the stored procedures are still attempting to update this table which cannot happen due to no password being fed to the table. I can't say if your install kept the password field on the Users table but mine did and I had to make that adjustment. This might be the reason your users kept going out of sync is because an error is thrown on the procedure that adds the users to the older Users tables (Users, UserPortals, UserRoles).

I've noticed that even though I've logged a call for this issue, still not assigned? On this issue I believe I've found another problem regarding assignment to roles in the membership tables only; when attempting to add a user to a role at times various usernames aren't updating. They do in the UserRoles table as if you edit the user directly and look at his/her role, it will show properly however it does not in the security roles.

Re: User Accounts is currently unavailable

DanaNield — Tue, 01 Nov 2005 03:09:25 GMT

Hi Folks,

I recently upgraded a DNN site from 2.1.2 to 3.1.1 and I received an email from someone attempting to register today. He received this message:

The username specified is invalid. Please specify a valid username.

1. I spent the day deleting users in mass. Our site is for paid members and the majority of unapproved accounts were from non-members.
2. I had several a child portal with members when the site was 2.1.2. I did delete several of them.
3. I've run the SQL queries posted earlier with no errors and no change. New Users still get the above message.

Any help would be greatly appreciated.

Dana

Re: User Accounts is currently unavailable

razorphreak — Mon, 17 Oct 2005 15:10:16 GMT

I think I've got it down right on issue DNNP-2045. I'm also noticing issues assigning users to roles.

Re: User Accounts is currently unavailable

J7Mitch — Fri, 14 Oct 2005 23:08:50 GMT

razorphreak wrote:
But this isn't the bug...

The bug is if you are deleted from a portal you are deleted from everything. If the portal itself is deleted all users within that portal are deleted from the DNN users table. The probelm is however they are NOT deleted from the membership ones. What this creates is out of sync users who must be cleared from the membership tables BEFORE they can ever become a user in membership again. This was not resolved around 3.1.0 and I'm wondering was it in 3.1.1. This is an issue because we are having this very problem now.

The bug in this thread is what I have been talking about.
I hadn't heard of the one you are talking about, has it been logged? http://support.dotnetnuke.com