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.
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.
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.
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.
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.
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...:-)
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.
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.