Unwanted Database Schema Addition - Front- & Back-End Development - Front- & Back-End Development - Progress Community
 Front- & Back-End Development

Unwanted Database Schema Addition

  • Unwanted Database Schema Addition
  • When first I setup the Sitefinity project, in IIS, the app pool was using one of my Windows AD users, call it A.  Later, we modified database permissions, and I started using another of my accounts, call it B.  When the Sitefinity database was originally created, all the tables were created with the schema dbo.  Now, occasionally, when performing an action in the backend, the system creates an entire duplicate copy of the tables, with schema B, which seems to break everything, because then I can’t even login to the backend, because the B schema doesn’t contain any users.  By deleting all of the B schema tables, I can recover, but I would like to fix the problem.  Every time I try to create a new Search Index, for example, a new copy of tables using the B schema is created, and therefore I cannot create a new Search Index successfully.  Thank you for any guidance, I imagine there is a simple resolution.
  • What is the default schema of the user who is used from Sitefinity for authentication in the SQL server. You can check that in the Sql Server -> Security -> Logins -> Selected User -> User Mappings  and there will be a field called Default Schema it should have value - "dbo"
  • This response was helpful and enabled me to resolve the problem, thank you.  In our case, there is no user Login at the server level, because the users are administered by an AD group.  The group had no default schema for the Sitefinity database.  I tried setting the default schema to dbo, but the result was the same when I tried to create a Search Index (duplicate set of tables with unwanted schema).  I also tried manipulating the Owned Schemas at the database level without an effect.

    I have switched to using a "service" AD account with dbo default schema and all is working properly.  This setup creates problems when developing with Entity Framework (due to cross-AD-domain limitations), but we should be able to switch to our "B" users when undertaking that development, and then back to the service account when manipulating the backend.  Many thanks.