Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Unable to set View permissions for All Users (version 7.4.2)Unable to set View permissions for All Users (version 7.4.2)
Previous
 
Next
New Post
10/19/2015 9:22 PM
 

When I try to set view permissions on a page I get the following errors.

Cannot insert duplicate key row in object 'dbo.TabPermission' with unique index 'IX_TabPermission_Users'. The duplicate key value is (, 103, 3). The statement has been terminated.

I found bug DNN-4869 that references this behavior in 7.3  (this was an upgrade from 7.2.2 to 7.4.2) but there is no indication how to clear the error... Supposedly it was fixed in 7.3.0.164.

This is an error on about 20+ pages and I'm thinking of just wiping and resetting the permissions but I'm too rusty - can't remember how I used to do that safely (boy... getting old sucks!)

 
New Post
10/20/2015 12:48 AM
 

I had a similar issue on an installation with a different index after 7.4.2.

My solution was to backup the DB.  Repeat backup the DB.  

Then I scripted out my offending index to a Query Window in SQL Manager.  Then I dropped the index from the DB.  The page error disappeared and the db populated with non index offending values.  Then I created the index again.  No problems since.  

It helps to have a select with the columns matching the index values so you can see what is changing before and after.  In my case I was comfortable with what I saw but you'll want to be backed up and ready to roll back.

I think if I was working on that index I would be looking at what your seeing that matches the clustered index values .  The select below has the 1st 3 columns that match your index and the sort order also may help you see what's happening a little easier...    You might be able to do a manual SQL update if your comfortable with it and recognize the problem.   

 

SELECT TOP 1000 [UserID]

      ,[TabID]

      ,[PermissionID]

      ,[TabPermissionID]

      ,[AllowAccess]

      ,[RoleID]      

      ,[CreatedByUserID]

      ,[CreatedOnDate]

      ,[LastModifiedByUserID]

      ,[LastModifiedOnDate]

  FROM [YourDBName].[dbo].[TabPermission]

  order by [UserID][TabID][PermissionID]

 
New Post
10/20/2015 3:31 AM
 

DNN-4869 refers to a different index, in your case it is referring to the user index, which should be filtered to records with userid not null.
you may drop and recreate the index by running the following script in Host > SQL:

IF EXISTS(SELECT * FROM sys.indexes WHERE name = N'IX_{objectQualifier}TabPermission_Users' AND object_id = OBJECT_ID(N'{objectQualifier}TabPermission'))
DROP INDEX [IX_{objectQualifier}TabPermission_Users] ON {databaseOwner}[{objectQualifier}TabPermission]
GO

-- make sure, table content meets requirements:
UPDATE      {databaseOwner}[{objectQualifier}TabPermission]           SET UserId = Null WHERE UserId = -1
UPDATE      {databaseOwner}[{objectQualifier}TabPermission]           SET RoleId = Null WHERE UserId Is Not Null
DELETE FROM {databaseOwner}[{objectQualifier}TabPermission]           WHERE RoleId                Is Not Null AND NOT RoleId                IN (SELECT RoleId                FROM {databaseOwner}[{objectQualifier}Roles]               )
DELETE FROM {databaseOwner}[{objectQualifier}TabPermission]           WHERE UserId                Is Not Null AND NOT UserId                IN (SELECT UserId                FROM {databaseOwner}[{objectQualifier}Users]               )
DELETE FROM {databaseOwner}[{objectQualifier}TabPermission]           WHERE TabId                 Is Null      OR NOT TabId                 IN (SELECT TabId                 FROM {databaseOwner}[{objectQualifier}Tabs]                )
DELETE FROM {databaseOwner}[{objectQualifier}TabPermission]           WHERE PermissionID          Is Null      OR NOT PermissionID          IN (SELECT PermissionID          FROM {databaseOwner}[{objectQualifier}Permission]          )
-- Delete duplicate records:
DELETE FROM {databaseOwner}[{objectQualifier}TabPermission]     	  WHERE TabPermissionID NOT IN
 (SELECT Min(TabPermissionID) FROM {databaseOwner}[{objectQualifier}TabPermission] GROUP BY TabID, PermissionID, RoleID, UserID)
-- Make sure, there are no records with userID and roleID assigned any more:
DELETE FROM {databaseOwner}[{objectQualifier}TabPermission] 
	WHERE (UserID IS Not Null AND RoleID IS Not Null)
	OR	  (UserID IS     Null AND RoleID IS     Null)
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_{objectQualifier}TabPermission_Users ON {databaseOwner}[{objectQualifier}TabPermission]
(UserID, TabID, PermissionID)
INCLUDE (AllowAccess)
WHERE UserID IS NOT Null
GO

Cheers from Germany,
Sebastian Leupold

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Unable to set View permissions for All Users (version 7.4.2)Unable to set View permissions for All Users (version 7.4.2)


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out