U4-2558 - setting user permissions on content nodes corrupts them, and then no children allowed

Created by Promoscience 29 Jul 2013, 10:35:57 Updated by Shannon Deminick 24 Mar 2014, 23:22:43

Hi, I recently updated my website to Umbraco 6.1.3 to resolve the U4-2161 bug, but now I can't create any children for the nodes where i set permissions for a restricted user.

In particular, when creating a new node, after setting the name and the document type, i get alternately the errors: -Parameters supplied for object 'umbracoUser2NodePermission' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required. Exception Details: System.Data.SqlClient.SqlException: Parameters supplied for object 'umbracoUser2NodePermission' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required. -Incorrect syntax near ','. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.

I replicated the error with a brand new installation of umbraco 6.1.3 and with an empty database. I set up an usertype with no default permissions; then set "Browse node" permission to the first level node for the limited user. Now either the admin nor the user can create nodes below the first level node without the errors above.

Comments

Bradley Kronson 30 Jul 2013, 09:12:45

Seen the same issue, cannot add any content in the CMS now


Bradley Kronson 30 Jul 2013, 09:39:39

Just a sample of the code from what seems to be the offending SQL line

exec sp_executesql N'INSERT INTO [umbracoUser2NodePermission] () VALUES (@0,@1,@2), (@3,@4,@5), (@6,@7,@8), (@9,@10,@11), (@12,@13,@14), (@15,@16,@17), (@18,@19,@20), (@21,@22,@23), (@24,@25,@26), (@27,@28,@29), (@30,@31,@32)',N'@0 int,@1 int,@2 nvarchar(4000),@3 int,@4 int,@5 nvarchar(4000),@6 int,@7 int,@8 nvarchar(4000),@9 int,@10 int,@11 nvarchar(4000),@12 int,@13 int,@14 nvarchar(4000),@15 int,@16 int,@17 nvarchar(4000),@18 int,@19 int,@20 nvarchar(4000),@21 int,@22 int,@23 nvarchar(4000),@24 int,@25 int,@26 nvarchar(4000),@27 int,@28 int,@29 nvarchar(4000),@30 int,@31 int,@32 nvarchar(4000)',@0=6,@1=5211,@2=N'S',@3=7,@4=5211,@5=N'5',@6=7,@7=5211,@8=N'A',@9=7,@10=5211,@11=N'C',@12=7,@13=5211,@14=N'F',@15=7,@16=5211,@17=N'K',@18=7,@19=5211,@20=N'P',@21=7,@22=5211,@23=N'R',@24=7,@25=5211,@26=N'S',@27=7,@28=5211,@29=N'U',@30=7,@31=5211,@32=N'Z'

Should there be empty brackets after [umbracoUser2NodePermission] ??


Bradley Kronson 30 Jul 2013, 09:40:19

Stack Trace:

[SqlException (0x80131904): Parameters supplied for object 'umbracoUser2NodePermission' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2062078 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5050204 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +215 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() +71 Umbraco.Core.Persistence.PetaPocoExtensions.BulkInsertRecords(Database db, IEnumerable1 collection) +400 Umbraco.Core.Persistence.Repositories.PermissionRepository2.AssignEntityPermissions(TEntity entity, IEnumerable1 userPermissions) +174 Umbraco.Core.Persistence.Repositories.ContentRepository.PersistNewItem(IContent entity) +1096 Umbraco.Core.Persistence.Repositories.RepositoryBase2.PersistNewItem(IEntity entity) +54 Umbraco.Core.Persistence.UnitOfWork.PetaPocoUnitOfWork.Commit() +235 Umbraco.Core.Services.ContentService.CreateContentWithIdentity(String name, Int32 parentId, String contentTypeAlias, Int32 userId) +332 umbraco.cms.businesslogic.web.Document.MakeNew(String Name, DocumentType dct, User u, Int32 ParentId) +127 umbraco.contentTasks.Save() +116 umbraco.presentation.create.dialogHandler_temp.Create(String NodeType, Int32 TypeId, Int32 NodeId, String Text) +385 umbraco.cms.presentation.create.controls.content.DoCreation() +160 umbraco.cms.presentation.create.controls.content.sbmt_Click(Object sender, EventArgs e) +5 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563


Sebastiaan Janssen 30 Jul 2013, 13:55:22

I have no problems with setting user permissions anywhere, logged in as admin or editor and the method mentioned in the stack track (AssignEntityPermissions) completes just fine for me. Any possibility that you can zip up the site + database for me and put it somewhere I can download it? You can comment with a link here, make sure to set "Visible to" to "HQ" so that it's a private comment.


Sebastiaan Janssen 30 Jul 2013, 14:52:10

Thanks both for sending me your sites. Good news and bad news: When I use your databases against the nightly build of 6.1.4 the error does not appear (and yes, I've verified that the error does appear with 6.1.3 final).

Bad news: I'm not sure what fixed this, so I'll let Shannon have a glance at it to make sure it's actually fixed.

6.1.4 is scheduled for release on August 22nd so that's a few weeks away. So in the mean time the only recommendation I can give you is to upgrade to the nightly if you need this to work now.


Sebastiaan Janssen 30 Jul 2013, 15:29:55

And here's the link to the nightly build you can use: http://nightly.umbraco.org/umbraco%206.1.4/UmbracoCms.6.1.4-build.20.zip


Bradley Kronson 30 Jul 2013, 15:55:13

Thanks, the nightly works for me!


Shannon Deminick 31 Jul 2013, 00:14:00

Its a fix with the bulk sql insert statement in 6.1.4, but the craziest thing is that we have 2 unit tests for this in 6.1.3 and I did test it of course. In any case, looks like there's obviously an issue with it in 6.1.3 :(

The other good news is that you'll also get some performance enhancements in the 6.1.4 night build too: http://issues.umbraco.org/issue/U4-2527


Bradley Kronson 31 Jul 2013, 06:52:08

Two for the price of one, thanks again....


Ana Rita Santos 31 Jul 2013, 14:46:38

Sorry. But I still can't create new nodes...even with version 6.1.4. I don't know what I'm doing wrong. I replaced the /bin, /umbraco and /umbraco_client folders. The only way I got it working was by deleting all rows from table umbracoUser2NodePermission. This isn't a solution. I need to place permissions. Thanks.


Ana Rita Santos 31 Jul 2013, 15:31:03

Went back to version 6.1.2. Thanks.


Sebastiaan Janssen 31 Jul 2013, 16:00:05

@Ana I'd be happy to have a look if you can upload your site and database somewhere. You can comment with a link here, make sure to set "Visible to" to "HQ" so that it's a private comment.


Shannon Deminick 01 Aug 2013, 00:15:49

Hi @Ana, can you please provide more details on what was not working for you? Were you receiving an error, stack trace, etc... that you can provide us? What database type and version are you using (i.e. SQL Server 2008? Sql Ce ?)


Jason Provence 01 Aug 2013, 19:03:10

I'm having the same issue as Ana. SQL Server 2005. Nightly build did not work for me either. I don't really have a great way to share the site/db but I'll try to work something out if the below isn't helpful.

Stack trace below:

[SqlException (0x80131904): Incorrect syntax near ','.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +404 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6388109 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6389810 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +538 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +689 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +327 StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() +91 Umbraco.Core.Persistence.PetaPocoExtensions.BulkInsertRecords(Database db, IEnumerable1 collection, Transaction tr) +823 Umbraco.Core.Persistence.PetaPocoExtensions.BulkInsertRecords(Database db, IEnumerable1 collection) +187 Umbraco.Core.Persistence.Repositories.ContentRepository.PersistNewItem(IContent entity) +1258 Umbraco.Core.Persistence.Repositories.RepositoryBase`2.PersistNewItem(IEntity entity) +66 Umbraco.Core.Persistence.UnitOfWork.PetaPocoUnitOfWork.Commit() +324 Umbraco.Core.Services.ContentService.CreateContentWithIdentity(String name, Int32 parentId, String contentTypeAlias, Int32 userId) +364 umbraco.cms.businesslogic.web.Document.MakeNew(String Name, DocumentType dct, User u, Int32 ParentId) +202 umbraco.contentTasks.Save() +115 umbraco.presentation.create.dialogHandler_temp.Create(String NodeType, Int32 TypeId, Int32 NodeId, String Text) +755 umbraco.cms.presentation.create.controls.content.DoCreation() +225 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3707


Jason Provence 01 Aug 2013, 19:10:07

I would go back to 6.1.2 like Ana, but we really needed the permission inheritance fix in 6.1.3. We've only upgraded on our test site at the moment, but for a site with dozens of content editors, both of these have been show stoppers.


Shannon Deminick 01 Aug 2013, 23:59:54

Hey guys,

You are probably getting this error because Umbraco's minimum requirements are SQL Server 2008. 2005 does not support bulk insert statements just like Sql Ce, without them this process is very slow (which is why we don't recommend SQL CE for larger sites).

That said, I can put a check in there for < 2008 and make it operate like SQL CE, but it will be much slower. I strongly recommend that you upgrade to SQL Server 2008+


Shannon Deminick 02 Aug 2013, 06:05:24

I've updated this in revision:

7e9cad34db0a1e745e7fe50e25bc2c191fb3418e

We will now do a SQL Server version check before attempting to bulk insert. If you have less than version 2008, it cannot bulk insert and will insert a row one at a time. Just to be clear on this though, if you have a large node-set this will have poor performance and it is highly recommended to upgrade to SQL server 2008.

I'll post the nightly download link in 30 minutes.


Ana Rita Santos 19 Aug 2013, 13:29:28

Sorry for the delay. Went on vacation, didn't have access to the web. My SQL server is SQL Express 2008. (v9.0.5000). Get the same stacktrace as Jason Provence


Sebastiaan Janssen 19 Aug 2013, 14:37:28

@Ana 6.1.4 will be out later this week, the issue is fixed there.


James Collins 21 Aug 2013, 14:08:29

I've found a (less than ideal) workaround for those who absolutely need to create a content node and are getting the umbracoUser2NodePermission error...

Create the node in an area that doesn't have specific user permissions, and then move the node into the required location. For us, this meant creating a node in absolute root of the site content tree, a child of Content, (without publishing), then moving it. After the move, we could populate content, and publish the node just fine.

Hope this works for others too.


Geoff Beaumont 21 Mar 2014, 14:55:37

I'm seeing the same issue in 6.1.6 on SQLAzure - I'm guessing this also doesn't support bulk insert and needs to fall back on the old method?


Shannon Deminick 24 Mar 2014, 00:23:46

Hi Geoff, we've just tested this against SQL Azure and it works as expected.


Geoff Beaumont 24 Mar 2014, 22:49:59

Hi Shannon,

Was that on Azure itself or a development environment? This site has been upgraded from an earlier version of v6 (I'd have to check the source control logs to see what it's been), but I don't think that should have any relevance to this issue?


Shannon Deminick 24 Mar 2014, 23:22:43

Hi, yes that was a test on Azure, if you can find the exact error logs/stack trace please post it.


Priority: Show-stopper

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 6.1.3

Due in version: 6.1.4

Sprint:

Story Points:

Cycle: