Permission error during database conversion

I’m upgrading a database from 2009 to 2016. In that process I first convert the database to 2013 format so I can export and import fob files in 2016 (which isn’t possible directly from 2009).

When converting the database I get a login error in the middle of the process:

Microsoft Dynamics NAV Development Environment
—————————
The following SQL Server error or errors occurred:
18456,”28000″,[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘MyDomain\MyUser’.
4060,”42000″,[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database ‘MyDatabase’ requested by the login. The login failed.

It turned out this had nothing to do with login failure. I have read on blogs and forums that it’s the reason should be linked tables in the database, but that was not my problem. It was a custom table that caused the problem. The only solution was to delete the table. I tried changing field names, datatypes, field lengths, deleting statistics etc. but nothing helped. After conversion I could import the table again.

To see which table that is causing the problem you can run SQL Profiler during the conversion and see where it stops.

Thanks for reading this. I’ll continue upgrading now :-)

5 Responses to “Permission error during database conversion”

  1. Natalie 2016-03-04 at 08:18 #

    Er, could you tell us what was actually wrong with the table you had to delete? Any idea?

    • Lars Westman 2016-03-04 at 11:04 #

      Not a clue actually. The table was a simple setup table that didn’t change during the upgrade. Exported it as a fob and deleted it. Then imported it back after the upgrade and populated the data again.

  2. Natalie 2016-03-04 at 11:16 #

    OK, thanks anyway. I would have guessed that maybe some special sign was used within a field name, or maybe a “forbidden” (depricated) property was populated.

    • Lars Westman 2016-03-04 at 11:19 #

      No. I thought of that also and made sure there where no changed properties on table or fields and the table name only included a..z.

      The big problem I think is the error message saying that it’s a permission error when it’s not.

  3. Lars Westman 2016-03-29 at 10:23 #

    And it happened again in the next upgrade…. This time a simple table with a code field and a description field. Nothing special about it. I deleted all statistics, deleted the data and still get the error.

    I can see in Profiler that it stops where the temporary table shall be created but I have no problem executing the same commands in Query Analyzer.

    I truncated the table and deleted it from Object Designer and the conversion succeeded. After the conversion to 2013 I imported the table and populated the data again.

    The error is thrown by SQL Server. I can see that in the event log. I’m on version 2014 of SQL.

    So far I’m only doing a test upgrade on my local machine. It will be interesting to see what happens when we do the conversion in the production database. I’ll report back on that.

Leave a Reply