Kine's Info

What I found and what I know about Microsoft Dynamics NAV

Pay attention to permissions when upgrading DB from SQL 2000 to SQL 2005

Story

Today I solved one "mystery" which leads to data loss. But from the beginning...

We have upgraded HW and MS SQL to version 2005 on 64bit platform. Everything without problems, because SAN was used, transferring the DB was just Detach/Attach and running one SSIS task to transfer user logins. No problem. After upgrade all is working without problems. But...

Some users started to complain that specific functionality is not working correctly. What's going on?

From one card you can open another form through button. This form shows some related records, and if there are no related records, they are generated. It's simple. But now, user opens the form and there is nothing in it. I tested it and it works ok, records were created. Ok, I have something to think about and something to solve.

After few rounds of "you'll try it, I'll try it" we noticed, that when the user open the form, there is just one line in the form, looking as inserted (no asterisk on left side of the table). But after we moved cursor or just opened table filters and returned back, there is no record in the table and we are on the new line. 8-|

Ok, code looks correctly, no conditions or something else preventing the record insertion process. I started client monitor and there are all the inserts statements, but still no records in the table.

I will not go deep into the analysis of this problem, if you are interested in what I did to discover the source of the problem, post comment and maybe I will wrote another article about that. Now, I will jump to the time, when I found out what's the problem.

As everytime, the SQL Profiler helped to discover the problem. In profiler I enabled the tracing of Server errors and exceptions and after I catched the process, I saw few red lines saying this:

Exception 74 Error: 262, Severity: 14, State: 4

User Error Message 74 SHOWPLAN permission denied in database 'blablabla'.

Ok, it confirmed one thing I noticed at very beginning – if you are db_owner, it is working. If not, you have the problem. After looking into permissions and comparing the permissions from DB created on SQL 2005 and on the transfered one, I found out that there are no Database permissions "Show Plan" and "References" for the application role $ndo$shadow.

Result

Why they are not there? Because these permissions doesn't exists in MS SQL 2000. They are assigned correctly when you create the DB on MS SQL 2005, but not when you transfer the database by Detach/Attach or Backup/Restore process. These permissions are not created even when running Synchronize all process from within NAV (using Standard security model of course).

Another thing I noticed is that this bug makes visible problems only if you are working with Maximized NAV windows, if you are working with form in normal size, all seems to work correctly.

Conclusion

After upgrading MS SQL 2000 to MS SQL 2005, if you are not creating new DB but using Detach/Attach or SQL Backup/Restore process to transfer the database, you need to grant "Show Plan" and "References" permissions for the application role "$ndo$shadow" on the database. Do not forget this else you can experience mysterious data lost and behavior.

Details of the bug

The data are generated in OnRun trigger of the second form. After this trigger is finished, the first form started to be updated, because the OnAfterGetCurrRecord trigger of this first form is called and there is CurrForm.UPDATECONTROLS command in it. Because this process is still running in context of the transaction started with the OnRun trigger of second form, and because there are SHOW PLAN statements in it and the SHOW PLAN permissions are not granted to the app. role, SQL server raise exception "permission denied" and the transaction is rolled back. But NAV client doesn't catch this exception, and this is the main problem. The first form refresh process is not started if you do not have maximized forms. But the permission exception is triggered every time the forms are using internally COUNTAPPROX to read expected record count (may be because the scrollbar size?) and these calls are canceled by this. User does not know anything about this permission error...

This bug is rare to experience, but the permissions problems can be source of another bug which is not so easy to find or even notice it.

Repro steps

  1. Create new DB on MS SQL 2005, restore Cronus DB into it
  2. Remove "Show Plan" permissions from $ndo$shadow app. role on the DB. (or you can detach come cronus database from MS SQL 2000 and attach it in MS SQL 2005 - the permissions will not be there, because they are not exist in MS SQL 2000)
  3. Import attached objects
  4. Run form 90001, maximize it (this bug makes a problem only when the windows are maximized!)
  5. Click "Test - Run Form"
  6. Second window will open (maximized) but the window has no entries (or just one, until you open filter window or move the cursor, after that there are no lines in the form)
  7. Restore the windows size, close the window opened in step 6
  8. On restored window (not maximized) click again "Test - Run Form"
  9. Second window will open but this time with entered entries - it means correctly.
COMMIT BUG repro.ZIP
  • Hi,

    Great post, explains a lot to clarify issues we experienced. We also had one or two issues like this when migrating from SQL Server 2000 to 2005 (detach, attach method) and spent quite some time scratching our heads over this.

    As you rightly said, db_owners did not have the problem. We solved it in the end by temporarily changing the secutity model from standard to advanced, and then immediately changed it back again to standard. This seemed to recreate permissions for the application role, and after that... No more problems.

    Regards

    Meint

  • Hey Kamil, this is a great post, and will help a lot of people. But I don't think this is a bug. MS do recommend that you don't detach a 2000 db an attach to 2005, but instead do a full restore.

    Having said that, of course we all do exactly this, detach restore, so this is going to be very valuable information.

  • It is a bug, because this is solved in SP1 for NAV 5.00 and MS will create KB article for this (based on the service incident I created for that). And if you mean full backup created by NAV, it is not the way each time - you can have views and linked tables et. which will not be transfered through NAV backup. And if you use SQL backup, the permissions will be still wrong...:-)

  • OK thanks Kamil for the update. I had always just followed the advise of a full NAV restore, but I can see that going forward, especially with Large databases, that a detach attach does need to be possible.