Missing REFERENCES permissions error (MS SQL) when trying to create a new page

[Florian Schmitz]

Hi,

when I try to create a new page, I get an SQL error ("unknown server error") when the template list is loading.

The logs say that it complains about a missing REFERENCES permissions on the  AO_9412A1_AOUSER object (this table doesn't exist in the database btw).

I did not grant this permission to my confluence database user initially, but that is fixed now. Still, the error remains. How can I fix this issue?

Stack below:

Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
    - name:Microsoft SQL Server
    - version:13.00.4001
    - minor version:0
    - major version:13
Driver:
    - name:Microsoft JDBC Driver 6.3 for SQL Server
    - version:6.3.0.0

com.microsoft.sqlserver.jdbc.SQLServerException: Die REFERENCES-Berechtigung wurde für das AO_9412A1_AOUSER-Objekt, confluence-Datenbank, dbo-Schema, verweigert.
    at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:53)
    at com.atlassian.mywork.host.upgrade.v8.UserIdMigrationUpgradeTask.upgrade(UserIdMigrationUpgradeTask.java:42)
    at com.atlassian.activeobjects.internal.ActiveObjectUpgradeManagerImpl$1.doInTransaction(ActiveObjectUpgradeManagerImpl.java:68)
    at com.atlassian.activeobjects.internal.ActiveObjectUpgradeManagerImpl$1.doInTransaction(ActiveObjectUpgradeManagerImpl.java:64)
    at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21)
    at com.atlassian.sal.spring.component.SpringHostContextAccessor$1.doInTransaction(SpringHostContextAccessor.java:71)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
    at com.atlassian.sal.spring.component.SpringHostContextAccessor.doInTransaction(SpringHostContextAccessor.java:68)
    at com.atlassian.confluence.spring.transaction.interceptor.ConfluenceSpringHostContextAccessor.access$001(ConfluenceSpringHostContextAccessor.java:21)
    at com.atlassian.confluence.spring.transaction.interceptor.ConfluenceSpringHostContextAccessor.lambda$doInTransaction$3(ConfluenceSpringHostContextAccessor.java:72)
    at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContextInternal(VCacheRequestContextManager.java:87)
    at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:71)
    at com.atlassian.confluence.spring.transaction.interceptor.ConfluenceSpringHostContextAccessor.doInTransaction(ConfluenceSpringHostContextAccessor.java:72)
    at sun.reflect.GeneratedMethodAccessor242.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
    at com.sun.proxy.$Proxy317.doInTransaction(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor242.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:136)
    at com.sun.proxy.$Proxy317.doInTransaction(Unknown Source)
    at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:18)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
    at com.sun.proxy.$Proxy895.execute(Unknown Source)
    at com.atlassian.activeobjects.internal.SalTransactionManager.inTransaction(SalTransactionManager.java:42)
    at com.atlassian.activeobjects.internal.AbstractLoggingTransactionManager.doInTransaction(AbstractLoggingTransactionManager.java:16)
    at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.executeInTransaction(EntityManagedActiveObjects.java:204)
    at com.atlassian.activeobjects.internal.ActiveObjectUpgradeManagerImpl.upgrade(ActiveObjectUpgradeManagerImpl.java:64)
    at com.atlassian.activeobjects.internal.ActiveObjectUpgradeManagerImpl.upgrade(ActiveObjectUpgradeManagerImpl.java:36)
    at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.upgrade(AbstractActiveObjectsFactory.java:87)
    at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:67)
    at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:32)
    at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:91)
    ... 9 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Die REFERENCES-Berechtigung wurde für das AO_9412A1_AOUSER-Objekt, confluence-Datenbank, dbo-Schema, verweigert.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:260)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:857)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:757)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7342)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2688)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:685)
    at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:410)
    at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2238)
    at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2294)
    at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2266)
    at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91)
    at net.java.ao.EntityManager.migrate(EntityManager.java:128)
    at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:51)

[Zak Laughton · Accepted solution]

Hi Florian,

Sorry to hear that loading the template list gives you a REFERENCES permission error!

I notice you mentioned the Confluence user did not originally have the REFERENCES permission. This may mean there are other permissions missing as well. According to the Confluence Database Setup for SQL Server process, the Confluence database user should have full create, read, and write permissions for the database tables.

As initial troubleshooting, I recommend giving the database full create, read, and write permissions, then restarting Confluence.

If you still receive the error after updating permissions, I have a few questions to get a better understanding of the issue:

• What version of Confluence are you using?
• Is this a new issue, or has it always happened?
• If it's a new issue, were there any changes to your environment around the time this started happening?

Thanks!
Zak

[Florian Schmitz]

> then restarting Confluence

Restarting actually helped, thanks. That was kind of unexpected.

> the Confluence database user should have full create, read, and write permissions for the database tables.

Would have been great if you had specified the exact permissions needed. Then my mistake would not have happened in the first place (unfortunately there is no option "read / create / write" in SQL server, only a very extensive list of permissions).

[Zak Laughton]

Glad to hear the restart helped!

And sorry the Database Setup for SQL Server doc caused confusion for the database setup! I've raised this concern to our docs team to take into consideration in future edits.

Thanks!
-Zak