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.
Objects for download