CREATE TABLE permission denied in database Core/Master/Web upon executing Sitecore.Framework.Publishing.Host.exe schema upgrade --force

If you are setting up a Sitecore Publishing Service, sooner or later you would most likely need to execute the script: Sitecore.Framework.Publishing.Host.exe schema upgrade --force

There is a chance that the following issue would occur when you run that script.

C:\inetpub\wwwroot\alextestPublishingService42>Sitecore.Framework.Publishing.Host.exe schema upgrade --force

[23:48:16 INF] Schema Upgrade

[23:48:16 INF] Upgrading all databases to version [ 2 ]

[23:48:16 ERR] One or more errors occurred.

System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: CREATE TABLE permission denied in database 'XP0.930_Core'.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
...
omitted
...
at Sitecore.Framework.Publishing.Data.SchemaInstaller.<Upgrade>d__9.MoveNext()

The solution for this is to add the user (the one you specified in the connection string file "sc.connectionstrings.json" in <yourPublishingService>/config/global) into the role db_ddladmin from SQL.

For some reason running the SQL query to add something into the role doesn't work.

I did several other tests such as (I got from this article):

USE [XP0.930_Master];

EXEC sp_addrolemember 'db_ddladmin','masteruser'; 

But the SSMS produces:

Msg 15151, Level 16, State 1, Line 10

Cannot alter the role 'db_ddladmin', because it does not exist or you do not have permission.

After clicking and checking some places in my SSMS, I found another workaround for this which is to add the role manually. The steps look like this:

Doing this to all the 3 databases should help to solve the issue.

When everything is good, you would receive something similar like this:

I hope this helps anyone with the same issue.


* I didn’t manage to investigate the reason why adding the user to the role manually works whereas executing the SQL stored procedure doesn’t. There is also another similar post for that issue with different SQL query.