Home NewsX Error SQL71627 The element Permission has property Permission set to a value that is not supported in Microsoft Azure SQL Database v12

Error SQL71627 The element Permission has property Permission set to a value that is not supported in Microsoft Azure SQL Database v12

by info.odysseyx@gmail.com
0 comment 3 views


There was a support call in which a customer encountered an error when trying to export an Azure SQL Database to a bacpac file using the SqlPackage command line utility.

error message:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2024-08-21T16:10:56 : Microsoft.SqlServer.Dac.DacServicesException: One or more unsupported elements were found in the schema used as part of the data package.

Error SQL71627: The Permission property of the Permission element is set to a value that is not supported in Microsoft Azure SQL Database v12.


The root cause is “receive” Types of permissions that existed in the object’s database Query notification error queue. If you have this permission, an error occurred while exporting the database.

This permission can be found through a query. sys.database_permissions. For example, use the following query:

SELECT pr.principal_id
    ,pr.name [PrincipalName]
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name
    ,s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE PE.permission_name="RECEIVE"

Roberto Yonekawa_0-1728449244269.png

way out:
You will need to remove this permission to fix the problem. Unsubscribe to QueryNotificationErrorsQueue [UserName]
After removing that permission, bacpac export succeeded.

In this case, the customer had no idea where this permission came from. Chances are your database was used with query notifications and Service Broker in an on-premises environment at some time in the past. This permission is mentioned in the following link: Query Notification Permissions

The same error occurs when exporting a database as a bacpac file through SSMS and also with the Azure portal import/export service.
Previous version of SSMS (version For example 17.9.1) It appears to be unaffected and you will not receive this error.





Source link

You may also like

Leave a Comment

Our Company

Welcome to OdysseyX, your one-stop destination for the latest news and opportunities across various domains.

Newsletter

Subscribe my Newsletter for new blog posts, tips & new photos. Let's stay updated!

Laest News

@2024 – All Right Reserved. Designed and Developed by OdysseyX