Does the database user have access to the schema that the database was created with?

We had a thread about this very issue yesterday on Discord: https://discord.com/channels/869656431308189746/882984410432012360/1024331044322803814

I'll copy in the convo:

LebSeaKyle — Yesterday at 4:35 PM Anyone know of a reason why umbraco 9 would ask to create a new database when a proper connection string is configured, and is correct?

Sebastiaan (he, him) — Yesterday at 4:45 PM Connectionstring is in the correct appSettings.{environment}.json and you're sure the environment variable for {environment} is set?

LebSeaKyle — Yesterday at 4:46 PM @Sebastiaan (he, him) I have an appsettings.json with the right connection string no appsettings.{environment}.json file exists, should it? where would i ensure the environment variable is set correctly? sorry bit bug eyed at the moment after looking and trying so long

Sebastiaan (he, him) — Yesterday at 4:50 PM It depends on what your ASPNETCORE_ENVIRONMENT environment variable is configured as. If it's Release for example then it will look in appSettings.Release.json for your connection string. I think the fallback is probably appSettings.json but I can't remember for sure. Also, not sure how you verified the connection string is correct, but I like to verify it in Azure Data Studio (sort of the VSCode version of SQL Mgmt studio).

LebSeaKyle — Yesterday at 4:50 PM I verified by logging into the SQL Server with the user and made sure the DB was listed and that i could read the tables etc So i can connect to he DB using the correct user etc, umbraco just thinks that i need to install the DB again

Sebastiaan (he, him) — Yesterday at 4:51 PM You can check the env variable with echo $Env:ASPNETCORE_ENVIRONMENT but on local machine it's usually probably empty.
Are you sure it's the same user that created the database? What's the schema? And what schema does the user you're trying this with use? Usually if you use sa it will create with dbo schema, but if you use user kyle it would probably only be able to access [kyle].[tablename] tables.

LebSeaKyle — Yesterday at 4:53 PM the schema is dbo for the user should the user have the dbo owner in owned schemas? its mapped to be db owner in the membership

Sebastiaan (he, him) — Yesterday at 4:54 PM Hence the hint to try Azure Data Studio where you can paste an exact connection string and see if it definitely works 🙂 I honestly can't remember how to properly configure SQL server users, sorry 😅

LebSeaKyle — Yesterday at 4:54 PM all good the user isnt out of the ordinary for our setup, aka everything to my eyes looks legit Sebastiaan (he, him) — Yesterday at 4:55 PM Trust, but verify 😉

LebSeaKyle — Yesterday at 4:55 PM for sure thanks @Sebastiaan (he, him) @Sebastiaan (he, him) azure data studio connected perfectly ine should I see tables within that? Sorry never used it before

Sebastiaan (he, him) — Yesterday at 5:01 PM Yes! Something like this:

If you don't see tables your user doesn't have access to them. Another way to check would be to take a full backup of the database and letting Umbraco install, then seeing what the schema (table prefix) is. Probably not dbo 🙂

LebSeaKyle — Yesterday at 5:04 PM hmm the user cant see the tables lol yeah the table prefix is dbo_owner!

Sebastiaan (he, him) — Yesterday at 5:05 PM Yep, so Umbraco can't see them either 😅