sqlpackage /Action:Extract with /p:IgnorePermissions=false throws InvalidCastException (SqlSecurityPolicy -> ISqlSecurable) when a SECURITY POLICY has an object-level permission

[arnavrupde1]

Summary

When a database contains a Row-Level Security SECURITY POLICY that has an object-level permission granted on it (e.g. GRANT VIEW DEFINITION ON [schema].[policy]), sqlpackage /Action:Extract run with /p:IgnorePermissions=false aborts while reverse-engineering the permission:

*** Error extracting database:Could not extract package from specified database.
Error 0: An error occurred while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPermissionStatement: Unable to cast object of type 'Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSecurityPolicy' to type 'Microsoft.Data.Tools.Schema.Sql.SchemaModel.ISqlSecurable'.

The SQL Server engine treats a security policy as an ordinary object securable: GRANT VIEW DEFINITION ON [schema].[policy] is accepted and stored in sys.database_permissions with class = 1 (OBJECT_OR_COLUMN) and major_id = the policy's object_id. However, DacFx's SqlSecurityPolicy model type does not implement ISqlSecurable, so reverse-engineering that permission throws an InvalidCastException.

Steps to reproduce

1. Create the schema (any SQL Server 2016+ or Azure SQL Database):

CREATE TABLE dbo.T (Id INT PRIMARY KEY, TenantId INT);
GO
CREATE FUNCTION dbo.fn_pred(@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok WHERE @TenantId = 1;
GO
CREATE SECURITY POLICY dbo.P
    ADD FILTER PREDICATE dbo.fn_pred(TenantId) ON dbo.T
    WITH (STATE = ON, SCHEMABINDING = ON);
GO
-- The trigger: any object-level permission granted on the security policy
CREATE USER auditor WITHOUT LOGIN;
GRANT VIEW DEFINITION ON [dbo].[P] TO [auditor];
GO

2. Extract with permissions included → FAILS:

sqlpackage /Action:Extract \
  /SourceServerName:"<server>" \
  /SourceDatabaseName:"<db>" \
  /SourceUser:"<user>" \
  /SourcePassword:"<password>" \
  /TargetFile:/tmp/repro.dacpac \
  /p:IgnorePermissions=false

Actual result

Extracting schema
Extracting schema from database
*** Error extracting database:Could not extract package from specified database.
Error 0: An error occurred while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPermissionStatement: Unable to cast object of type 'Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSecurityPolicy' to type 'Microsoft.Data.Tools.Schema.Sql.SchemaModel.ISqlSecurable'.

Unable to cast object of type 'Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSecurityPolicy' to type 'Microsoft.Data.Tools.Schema.Sql.SchemaModel.ISqlSecurable'

Expected result

The extract should succeed and capture the permission, since the engine permits and stores object-level permissions on a security policy. SqlSecurityPolicy should implement ISqlSecurable (as other grantable schema objects do) so the permission round-trips.

Contrast / current behavior with the default

The same extract with /p:IgnorePermissions=true (the default) succeeds, but silently drops the policy's permission — the generated .dacpac model.xml contains the SqlUser and the SqlSecurityPolicy but no SqlPermissionStatement for the grant. So a policy's object permissions today either crash the extract (IgnorePermissions=false) or are silently lost (IgnorePermissions=true), and never round-trip.

Environment

• sqlpackage version: 170.3.93.6
• .NET runtime: 8.0.303
• OS: Ubuntu 20.04.6 LTS (Linux 5.4.0-216-generic, x86_64)
• Source: Azure SQL Database (also reproduces on SQL Server 2016+)
• Action / property: Extract, /p:IgnorePermissions=false