CODEWITHSUNDEEP
azurevisual-studio-2017azure-sql-database
Mehroz Irshad
Mehroz Irshad

Reputation: 307

Error While Publishing to Azure

I am trying to publish some changes to an Azure App Service including database migrations as well but I am experiencing following error does any have have any clue what's this about. P.S The database user does not have sysadmin server role assigned.

1>Warning SQL0: A project which specifies SQL Server 2016 as the target 
platform may experience compatibility issues with Microsoft Azure SQL 
Database v12.
1>Warning SQL0: Your permissions to see all objects in the server or 
database could not be verified.  The original error was:
1>The SELECT permission has not been granted on 'sys.sql_logins' for the 
'master' database. You must be a member of the 'loginmanager' role to access 
this system view. 
1>Reverse Engineer will continue the import process, and logins will not be 
imported.
1>The reverse engineering operation will attempt to continue anyway, but the 
resulting model might be incomplete, malformed, or incorrect.
1>Error SQL72014: .Net SqlClient Data Provider: Msg 5011, Level 14, State 2, 
Line 5 User does not have permission to alter database 'Iklikl_Staging', the 
database does not exist, or the database is not in a state that allows 
access checks.
1>Error SQL72045: Script execution error.  The executed script:
1>IF EXISTS (SELECT 1
1>           FROM   [sys].[databases]
1>           WHERE  [name] = N'$(DatabaseName)')
1>    BEGIN
1>        ALTER DATABASE [$(DatabaseName)]
1>            SET TEMPORAL_HISTORY_RETENTION ON 
1>            WITH ROLLBACK IMMEDIATE;
1>    END

Upvotes: 2

Views: 4423

Answers (2)

PontiusTheBarbarian
PontiusTheBarbarian

Reputation: 1030

The SQL user you are using for deployments isn't part of the loginmanager role. loginmanager and dbmanager are two roles that are exclusive to Azure SQL.

Run the following against master (where [user] is your sql user): 

ALTER ROLE loginmanager ADD MEMBER [user];

You can validate your user now is part of the correct role by running the following query:

select dp.name as db_role, dp2.name as db_user
from sys.database_role_members drm
join sys.database_principals dp on (drm.role_principal_id = dp.principal_id)
join sys.database_principals dp2 on (drm.member_principal_id = dp2.principal_id)
order by dp.name

The user may also need other permissions such as 'Alter', 'Select' and 'View Definition' which you can add by running the following example command against your db (where [user] is your sql user :

GRANT ALTER TO [user]

Here are the articles I used (defo read the first one, the last two compliment the info in it):

https://www.sqlservercentral.com/articles/azure-dwh-part-12-permissions-roles-logins-and-users

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver15

Upvotes: 1

Murray Foxcroft
Murray Foxcroft

Reputation: 13745

You need to target SQL Azure.

From this article:

To change a project’s target platform

Right-click your project in Solution Explorer and select Properties. Click the Project Settings tab on the left to access the Project Settings property page.

The Target platform dropdown list in this page contains all the supported SQL Server platforms that a database project can be published to. For this procedure, select SQL Azure.

Upvotes: 1

Related Questions