I have recently upgraded my Azure SQL servers to v12 after the general availability in Europe. To my horror I have just discovered that I cannot import an exported database. The export itself seems to work fine, but importing it again using the new Azure portal (on the same server), consistently yields the error:

"The SELECT permission was denied on the object 'security_policies', database 'mssqlsystemresource', schema 'sys'."

I have also tried importing it using SSMS 2014 with CU5; also unsuccessful. I have further tried to import and export between SQL Azure v12 and a local SQL 2014 developer edition - every scenario fails.

Does anyone have a suggestion on how to get a bacpac imported into SQL Azure v12? I have been unable to figure out whether this is a general issue, as I cannot find much information on this error. I did find a link with the exact same error, although no viable solution is presented: http://www.designlimbo.com/?p=381

This is quite an important feature for me, and as such, I did do extensive testing before executing the upgrade. Before the upgrade my import/export tests on a v12 server were all successful. This was around february 1.

  We would like to ask you for more details in order to help us reproduce and investigate this failure.

  Can you please tell us what other features are you using? Can you please also try the following query on the affected database:

SELECT count(*) FROM sys.security_policies

SELECT count(*) FROM sys.security_predicates

  If you are not using RLS, we expect that the output for both queries is 0.

  Thanks,

-Raul Garcia

  SQL Server Security

  BTW. Please make sure you don't submit any sensitive information (PII, information under NDA, etc.) on this forum as this post is publicly accessible.

Hi,

I have a similar issue since we upgraded to v12. I am exporting from Azure v12 successfully but my bacpac restore now fails importing into Sql Server 2014 with :

Error SQL72014: .Net SqlClient Data Provider: Msg 2760, Level 16, State 1, Line 1 The specified schema name "sys" either does not exist or you do not have permission to use it.
Error SQL72045: Script execution error.  The executed script:
CREATE TYPE [sys].[script_deployment_databases] AS TABLE (
    [logical_server] [sysname] NOT NULL,
    [database_name]  [sysname] NOT NULL,
    UNIQUE NONCLUSTERED ([database_name] ASC, [logical_server] ASC));

This worked successfully before the upgrade.

Many thanks,

Mark.

Thank you for your reply.

The database is very simpel, containing just of tables, indexes and stored procedures. There are no features in use such as row level security or federation.

I can confirm that running the queries against the database yields 0 as the result output.

If I run the queries against master, though, the errors become:

The SELECT permission was denied on the object 'security_policies', database 'mssqlsystemresource', schema 'sys'.
The SELECT permission was denied on the object 'security_predicates', database 'mssqlsystemresource', schema 'sys'.

This may be expected? But it seemed like it may be relevant.

I get the exact same error as Mark when trying to import via SSMS 2014 CU5. I believe we are facing the same problem. Thank you for shedding some more light on the issue.

We are currently investigating this problem.

We will keep you updated.

Thanks,

-Raul

I've been having a similar issue and just found that dropping my users prior to creating my BACPAC resulted in me successfully being able to restore it on my v12 server.

Hope that helps..

Thank you for reporting these issues related to Import and upgraded servers. We have investigated and identified two issues:

Issue #1: Import fails if the .bacpac was created from a database on an upgradedserver. The users will see a failure with the following error message when importing such a .bacpac into either a new V12 or an upgraded server:

The specified schema name "sys" either does not exist or you do not have permission to use it.

 Issue #2.Import fails when the user will try to import a .bacpac containing certain types into a database on an upgraded server. The failure will contain the following error:

The SELECT permission was denied on the object 'security_policies', database 'mssqlsystemresource', schema 'sys'.

 We are working on a fix for both issues which will be deployed to production very soon.

-Raul.

We are also facing an error while trying to import. Unable to import a .bacpac generated from an upgraded server to a local MSSQL 2014 server.

I am getting the below error:

Is there a time frame for when this will be fixed?

Thanks

• Edited by alaatm Saturday, February 14, 2015 1:36 AM

We are also facing an error while trying to import. Unable to import a .bacpac generated from an upgraded server to a local MSSQL 2014 server.

I am getting the below error:

Is there a time frame for when this will be fixed?

Thanks

• Edited by alaatm Saturday, February 14, 2015 1:36 AM

We are also facing an error while trying to import. Unable to import a .bacpac generated from an upgraded server to a local MSSQL 2014 server.

I am getting the below error:

Is there a time frame for when this will be fixed?

Thanks

• Edited by alaatm Saturday, February 14, 2015 1:36 AM

We are also facing an error while trying to import. Unable to import a .bacpac generated from an upgraded server to a local MSSQL 2014 server.

I am getting the below error:

Is there a time frame for when this will be fixed?

Thanks

• Edited by alaatm Saturday, February 14, 2015 1:36 AM

We are also facing an error while trying to import. Unable to import a .bacpac generated from an upgraded server to a local MSSQL 2014 server.

I am getting the below error:

Is there a time frame for when this will be fixed?

Thanks

• Edited by alaatm Saturday, February 14, 2015 1:36 AM

Hello,

We are also facing an error while trying to import. Unable to import a .bacpac generated from an upgraded server to a local MSSQL 2014 server.

I am getting the following error:

Thanks

I'm trying to deploy a dacpac to an existing Azure SQL database using sqlpackage.exe, and it's giving me this error: 

** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission.
Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];


(None of my sql is directly referencing script_deployment_databases; I'm assuming that DROP statement is autogenerated at deployment time as part of deployment bookkeeping.)

I haven't explicitly upgraded the database; it's still at compatibility_level 100.

I only started encountering this problem late last week. Based on the timing, this smells related to all these other sys problems.

Here's the various seemingly-related problems I've started seeing since the 13th, with my own findings and attempts at analysis:

Problem 1:

All preexisting azure SQL databases suddenly have a new user defined table type: [sys].[script_deployment_databases]

• My bacpac backups show that the type didnt exist (or wasnt visible) before the 13th.
• It can be detected by querying on the particular afflicted database (not master): select * from sys.types where name = 'script_deployment_databases'
• Interestingly its marked with both is_user_defined=1 and the schema_id for [sys].
• The presence of this type causes dacpac deployments via sqlpackage.exe to fail, since sqlpackage attempts to drop it unsuccessfully.
• It causes restores of bacpac backups taken after the 13th to fail, since it tries to add the type unsuccessfully.
• New databases created on a preexisting server do not have this type (unless theyre created as a copy of an afflicted database).

Problem 1.b:

When trying to view Programmability -> Types -> User Defined Table Types in SSMS for preexisting azure SQL databases, it gives this error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) Additional information: unknown property IsMemoryOptimized (Microsoft.SqlServer.Management.Sdk.Sfc)

Problem 2:

Old rows in [sys].[dm_database_copies] appear to be stuck.

• Visible by querying on master db: select * from [sys].[dm_database_copies]
• On each of the 4 SQL servers we have, theres some number of entries in that table that have been stuck at 0 percent_complete for days, with the earliest start_date on the 13th.
  (We set up nightly backups long ago using the  Create Transactionally Consistent Copy, Then Create Bacpac approach, using homegrown code running on a worker role)

Problem 2.b:

Some databases are undeletable.

• As mentioned, we implemented a backup solution that uses database copies as the first step.
• Starting on the 13th, some of these copies are unable to be deleted from either the azure management portal or SSMS.
• Also, some copies appear only in SSMS or only in the portal.

Problem 3:

[sys].[event_log] overflow error.

• We have a worker role that periodically copies records from [sys].[event_log] on each azure SQL server for possible diagnostic use.
• Query is basically similar to this (run on master): select top 30 * FROM [sys].[event_log]    
• On 2 of our 4 servers, the query is always resulting in the error:
  The conversion of the nvarchar value '40613' overflowed an INT2 column. Use a larger integer column.
• The error started happening sometime around 2015-02-13 23:00 UTC

 

• Edited by mful Tuesday, February 17, 2015 11:11 PM formatting

Here's the various seemingly-related problems I've started seeing since the 13th, with my own findings and attempts at analysis:

Problem 1:

All preexisting azure SQL databases suddenly have a new user defined table type: [sys].[script_deployment_databases]

• My bacpac backups show that the type didnt exist (or wasnt visible) before the 13th.
• It can be detected by querying on the particular afflicted database (not master): select * from sys.types where name = 'script_deployment_databases'
• Interestingly its marked with both is_user_defined=1 and the schema_id for [sys].
• The presence of this type causes dacpac deployments via sqlpackage.exe to fail, since sqlpackage attempts to drop it unsuccessfully.
• It causes restores of bacpac backups taken after the 13th to fail, since it tries to add the type unsuccessfully.
• New databases created on a preexisting server do not have this type (unless theyre created as a copy of an afflicted database).

Problem 1.b:

When trying to view Programmability -> Types -> User Defined Table Types in SSMS for preexisting azure SQL databases, it gives this error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) Additional information: unknown property IsMemoryOptimized (Microsoft.SqlServer.Management.Sdk.Sfc)

Problem 2:

Old rows in [sys].[dm_database_copies] appear to be stuck.

• Visible by querying on master db: select * from [sys].[dm_database_copies]
• On each of the 4 SQL servers we have, theres some number of entries in that table that have been stuck at 0 percent_complete for days, with the earliest start_date on the 13th.
  (We set up nightly backups long ago using the  Create Transactionally Consistent Copy, Then Create Bacpac approach, using homegrown code running on a worker role)

Problem 2.b:

Some databases are undeletable.

• As mentioned, we implemented a backup solution that uses database copies as the first step.
• Starting on the 13th, some of these copies are unable to be deleted from either the azure management portal or SSMS.
• Also, some copies appear only in SSMS or only in the portal.

Problem 3:

[sys].[event_log] overflow error.

• We have a worker role that periodically copies records from [sys].[event_log] on each azure SQL server for possible diagnostic use.
• Query is basically similar to this (run on master): select top 30 * FROM [sys].[event_log]    
• On 2 of our 4 servers, the query is always resulting in the error:
  The conversion of the nvarchar value '40613' overflowed an INT2 column. Use a larger integer column.
• The error started happening sometime around 2015-02-13 23:00 UTC

 

• Edited by mful Tuesday, February 17, 2015 11:11 PM formatting

Here's the various seemingly-related problems I've started seeing since the 13th, with my own findings and attempts at analysis:

Problem 1:

All preexisting azure SQL databases suddenly have a new user defined table type: [sys].[script_deployment_databases]

• My bacpac backups show that the type didnt exist (or wasnt visible) before the 13th.
• It can be detected by querying on the particular afflicted database (not master): select * from sys.types where name = 'script_deployment_databases'
• Interestingly its marked with both is_user_defined=1 and the schema_id for [sys].
• The presence of this type causes dacpac deployments via sqlpackage.exe to fail, since sqlpackage attempts to drop it unsuccessfully.
• It causes restores of bacpac backups taken after the 13th to fail, since it tries to add the type unsuccessfully.
• New databases created on a preexisting server do not have this type (unless theyre created as a copy of an afflicted database).

Problem 1.b:

When trying to view Programmability -> Types -> User Defined Table Types in SSMS for preexisting azure SQL databases, it gives this error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) Additional information: unknown property IsMemoryOptimized (Microsoft.SqlServer.Management.Sdk.Sfc)

Problem 2:

Old rows in [sys].[dm_database_copies] appear to be stuck.

• Visible by querying on master db: select * from [sys].[dm_database_copies]
• On each of the 4 SQL servers we have, theres some number of entries in that table that have been stuck at 0 percent_complete for days, with the earliest start_date on the 13th.
  (We set up nightly backups long ago using the  Create Transactionally Consistent Copy, Then Create Bacpac approach, using homegrown code running on a worker role)

Problem 2.b:

Some databases are undeletable.

• As mentioned, we implemented a backup solution that uses database copies as the first step.
• Starting on the 13th, some of these copies are unable to be deleted from either the azure management portal or SSMS.
• Also, some copies appear only in SSMS or only in the portal.

Problem 3:

[sys].[event_log] overflow error.

• We have a worker role that periodically copies records from [sys].[event_log] on each azure SQL server for possible diagnostic use.
• Query is basically similar to this (run on master): select top 30 * FROM [sys].[event_log]    
• On 2 of our 4 servers, the query is always resulting in the error:
  The conversion of the nvarchar value '40613' overflowed an INT2 column. Use a larger integer column.
• The error started happening sometime around 2015-02-13 23:00 UTC

 

• Edited by mful Tuesday, February 17, 2015 11:11 PM formatting

Here's the various seemingly-related problems I've started seeing since the 13th, with my own findings and attempts at analysis:

Problem 1:

All preexisting azure SQL databases suddenly have a new user defined table type: [sys].[script_deployment_databases]

• My bacpac backups show that the type didnt exist (or wasnt visible) before the 13th.
• It can be detected by querying on the particular afflicted database (not master): select * from sys.types where name = 'script_deployment_databases'
• Interestingly its marked with both is_user_defined=1 and the schema_id for [sys].
• The presence of this type causes dacpac deployments via sqlpackage.exe to fail, since sqlpackage attempts to drop it unsuccessfully.
• It causes restores of bacpac backups taken after the 13th to fail, since it tries to add the type unsuccessfully.
• New databases created on a preexisting server do not have this type (unless theyre created as a copy of an afflicted database).

Problem 1.b:

When trying to view Programmability -> Types -> User Defined Table Types in SSMS for preexisting azure SQL databases, it gives this error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) Additional information: unknown property IsMemoryOptimized (Microsoft.SqlServer.Management.Sdk.Sfc)

Problem 2:

Old rows in [sys].[dm_database_copies] appear to be stuck.

• Visible by querying on master db: select * from [sys].[dm_database_copies]
• On each of the 4 SQL servers we have, theres some number of entries in that table that have been stuck at 0 percent_complete for days, with the earliest start_date on the 13th.
  (We set up nightly backups long ago using the  Create Transactionally Consistent Copy, Then Create Bacpac approach, using homegrown code running on a worker role)

Problem 2.b:

Some databases are undeletable.

• As mentioned, we implemented a backup solution that uses database copies as the first step.
• Starting on the 13th, some of these copies are unable to be deleted from either the azure management portal or SSMS.
• Also, some copies appear only in SSMS or only in the portal.

Problem 3:

[sys].[event_log] overflow error.

• We have a worker role that periodically copies records from [sys].[event_log] on each azure SQL server for possible diagnostic use.
• Query is basically similar to this (run on master): select top 30 * FROM [sys].[event_log]    
• On 2 of our 4 servers, the query is always resulting in the error:
  The conversion of the nvarchar value '40613' overflowed an INT2 column. Use a larger integer column.
• The error started happening sometime around 2015-02-13 23:00 UTC

 

• Edited by mful Tuesday, February 17, 2015 11:11 PM formatting

I am also having same issue trying to import the BACPAC file into my local SQL Server.  Any ideas yet?

(As of Wednesday 2015-February-18)

There is a known issue with the export of bacpac files from a Azure SQL Database server that has been upgraded to V12. Exported bacpac files will contain an errant object named script_deployment_databases. Bacpac files that contain this errant object cannot be imported by using the tools SQL Server Management Studio (SSMS), SqlPackage.exe, or the Data-Tier Application Framework (DacFx) API.

However, the Azure web portal can be used to import an affected bacpac file to Azure SQL Database. We expect to release a permanent fix for this issue during the week of Monday February 23 2015, including an update for the affected tools. In the interim, contact Microsoft Support if you need further assistance to recover an already exported file that has .bacpac as its file name extension.

In addition to the export issue, a limited number of servers and customers who recently upgraded to version V12 might experience a different error when attempting to import a bacpac file. This permissions-related error is transient and is normally resolved on an affected server within one day. We expect that this issue will also be permanently fixed during the week of Monday February 23 2015. In the interim, retrying your import operation might succeed. Contact Microsoft Support if you need further assistance importing a bacpac file to Azure SQL Database.

If necessary, you can follow these steps to contact Microsoft Support:

1. Browse to the Azure portal.
2. Right-click on the account name, found in the upper-right corner.
3. In the context menu that is displayed, click the item for support.
   • The item is probably labeled either Contact Microsoft Support or Help + support

For detailed and updated information please visit http://azure.microsoft.com/documentation/articles/sql-database-preview-plan-prepare-upgrade/ section C.3.1

Thanks,

-Raul Garcia

I did a export today, upgraded to SQL Server Management Studio 2014 CU6 but I am still seing the issue when trying to import? Wasn't it supposed to have been fixed last week?

Update (as of 2015-March-2):

The fixes for exporting and importing bacpacs from a V12-upgraded Azure SQL Database server have been deployed. You should be able to export/import successfully through the Azure portal, and you can install this updated version of DacFx to export/import through SSMS, SSDT, SqlPackage.exe, or the DacFx API.

http://www.microsoft.com/en-us/download/details.aspx?id=45886

(Note: You must install BOTH the x64 and x86 versions of the MSIs if you are on an x64 machine.)

Thank you for your patience.

-Raul

Hi Raul!

I've just followed the steps and now it gives me other errors different than before ones.

Let me paste the errors. As you could see it seems a particular error but the only thing I've done is exactly the same I did when it worked:

1.- Export a database from azure portal to a blob

2.- Import database from SSMS and select the blob where the backup was saved

I've been doing this way tons of times and it worked before V12!!

Hope it helps to find a final solution.

TITLE: Microsoft SQL Server Management Studio

------------------------------

Could not import package.
Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2012.
Warning SQL72012: The object [MyDb-2015-3-3-14-4_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
Warning SQL72012: The object [MyDb-2015-3-3-14-4_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
Error SQL72014: .Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 3 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Translations_dbo.Vouchers_Voucher_Id". The conflict occurred in database "MyDb-2015-3-3-14-4", table "dbo.Vouchers", column 'VoucherId'.
Error SQL72045: Script execution error.  The executed script:
PRINT N'Checking constraint: FK_dbo.Translations_dbo.Vouchers_Voucher_Id [dbo].[Translations]';

ALTER TABLE [dbo].[Translations] WITH CHECK CHECK CONSTRAINT [FK_dbo.Translations_dbo.Vouchers_Voucher_Id];


 (Microsoft.SqlServer.Dac)

------------------------------
BUTTONS:

OK
------------------------------

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful 13 hours 54 minutes ago describing discovered workaround

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful 14 hours 3 minutes ago describing discovered workaround

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful Thursday, March 05, 2015 6:08 PM describing discovered workaround

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful Thursday, March 05, 2015 6:08 PM describing discovered workaround
• Proposed as answer by ballistic2 9 hours 22 minutes ago

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful Thursday, March 05, 2015 6:08 PM describing discovered workaround
• Proposed as answer by ballistic2 Sunday, March 08, 2015 9:41 PM

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful Thursday, March 05, 2015 6:08 PM describing discovered workaround
• Proposed as answer by ballistic2 Sunday, March 08, 2015 9:41 PM

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful Thursday, March 05, 2015 6:08 PM describing discovered workaround
• Proposed as answer by ballistic2 Sunday, March 08, 2015 9:41 PM

I installed the new DacFx (both x64 and x86) and updated SSDT and am still getting the same issue.

I'm running:

"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:MyDatabaseName.dacpac  /Profile:MyDatabase.TestEnvironment.publish.xml /TargetPassword:ThisIsNotARealPassword

The assembly version of sqlpackage.exe is 12.0.2882.1

The error is:

Error SQL72014: .Net SqlClient Data Provider: Msg 218, Level 16, State 2, Line 1 Could not find the type 'sys.script_deployment_databases'. Either it does not exist or you do not have the necessary permission. Error SQL72045: Script execution error.  The executed script:
DROP TYPE [sys].[script_deployment_databases];

UPDATE: We discovered a workaround solution where we copy the database (using the "CREATE DATABASE [New] AS COPY OF [Existing]" SQL syntax), and then rename the existing one to "Old" and rename the new copy to replace the existing.

This database copy omits the errant object "script_deployment_databases", allowing DACPAC deployments to work.

(I had tried making database copies before, around the 17th, and the copies back then still contained the errant object. But now the copies are apparently "scrubbed clean" of the issue.)

• Edited by mful Thursday, March 05, 2015 6:08 PM describing discovered workaround
• Proposed as answer by ballistic2 Sunday, March 08, 2015 9:41 PM

Good news: Apparently the script_deployment_databases errant object is now gone.