Visual Studio 2022 cannot publish an Azure SQL database project

Question

I have created a DB project with VS2022 for an Azure SQL DB and everything works perfectly under my own credentials. I have also created a login (jdoe), a user (jdoe) under that login and I have given the db_owner role to jdoe.

When using jdoe login and password to publish or even create the SQL file it throws this error:

could not connect to myserver.database.windows.net mydatabase: the server principal "jdoe" is not able to access the database "master" under the current security context.

The funny part is "Test Connection" on PUBLISH wizard works just fine and gives OK, but when publishing it throws the above error.

What am I missing here? The db_owner right should be good enough for publishing to this user database, it does not make sense to give access to master db only for this

Answer 1

Could you please run the following query on the Master database to get the SID number associated with that login?

SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'jdoe'

After that run the following query on the Azure SQL user database, and collect what SID the user has on the user database?

SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE [type_desc] = SQL_USER'

Use the user name that has the matching SID on the master database. Logins that have a database user with a matching SID have user access to that database as that database user principal.

Try to set the following permissions for jdoe on the Master and user database, and try again to publish.

exec sp_addRoleMember 'db_owner', 'jdoe';