Hi mibagict,
Sorry that I didn't explained the issue properly.
The code of dbo.aspnet_UsersInRoles_AddUsersToRoles SP is pre-4.0 version that was loaded from
Providers\DataProviders\SqlDataProvider\InstallRoles.sql .
As a part of 4.0.0 update DNN ran Providers\DataProviders\SqlDataProvider\04.00.00.SqlDataProvider script,
which has
ALTER PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles and
ALTER PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
with lines
DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
Due to mismatch collation issue these ALTER PROCEDUREs failed, but DNN didn't show any warning for user(see DNNP-2596, incorrectly having status of withdrawn).
So the SPs are left out of date, and 4.0 code doesn't work with them. Again DNN doesn't report any error(see DNNP-2876 and my blog post - in particular DNNLibrary\Components\Security\Roles\RoleController.vb AddUserRole suggested change).
The steps to fix the SPs are the following:
1. Open Providers\DataProviders\SqlDataProvider\04.00.00.SqlDataProvider script in any editor.
2. Add COLLATE DATABASE_DEFAULT to DECLARE @tbNames table in both aspnet_UsersInRoles_AddUsersToRoles and aspnet_UsersInRoles_RemoveUsersFromRoles procedures to have
DECLARE @tbNames table(Name nvarchar(256) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY)
3. Copy the all ALTER PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles .
section to DNN Admin SQL page (or SQL Server MAnagement Studio)
4. Run ALTER PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles to apply changes to the database.
5. Repeat steps 3 and 4 for aspnet_UsersInRoles_RemoveUsersFromRoles
Let us know does it help.