:::: MENU ::::

Error message when creating assembly in a SQL Server database

  • Aug 28 / 2013
  • 0
dbDigger, SET Options, SQL Server Error messages

Error message when creating assembly in a SQL Server database

I was required to transfer SQL Server assembly along with the other objects from one SQL Server instance to another. I used Transfer SQL Server Objects which is a handy control of SSIS. During the test process i get following error as a result of trying to create assembly in target database.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: “ERROR : errorCode=-1073548784 description=Executing the query “CREATE ASSEMBLY [%]
AUTHORIZATION [dbo]
FRO…” failed with the following error: “CREATE ASSEMBLY for assembly ‘%’ failed because assembly ‘%’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}”.

This error is produced because before creating the assembly we have to mark the database as trustworthy. By default trustworthy mode is disabled for SQL Server databases. Trustworthy mode tells the server that this database contains controlled creation of objects and there is no chance that some one will create malicious objects in it or some one will manipulate it by attach/detach process. So if you are satisfied with above assumptions that you are going to give to your server then use following command in subject database and set it as a trustworthy database

ALTER DATABASE [DBNameHere] SET TRUSTWORTHY ON
GO

After this step we are now able to create and refer assemblies in SQL Server database and error exists no more.

Consult us to explore the Databases. Contact us