Changes to SQL tables

lloydsmods

I have a client going thru a new NAV implementation. They hired me to because they don't trust their reseller. They don't have any NAV people on staff and hired me to be an impartial eye to help with simple NAV tasks.

We recently discovered that users were suddenly unable to insert Vendors due to a SQL error. One user could not add a vendor (neither could I) but another could. Both were SUPER users in NAV. In SQL, though, one was a DB Admin and the other was not. The error was a SQL permissions error.

Overnight the table had been changed on the SQL side (not in NAV) to an older version. The older version had a trigger to sync data to other companies. I advised that they remove that trigger when I was on-site a couple of weeks ago, now it's back.

I'm not a SQL expert, so forgive me if I ask or say anything stupid. I assume it is possible to restore a database in SQL without going thru NAV? The client has reported (and complained to their reseller) about data entered into NAV which goes missing a week later. The reseller flatly denies restoring anything. Today we have evidence that a table was changed in SQL.

Why would you do it? What are the dangers (aside from what I've seen and what the client has complained about)?

satbir

If you do not trust then limit the user(s) who can make the changes to the database.

ThomasHej_MSFT

Yes, you can perform a SQL restore operation outside NAV, but Backup and Restore can also be done with the classic client (from NAV).

Limiting the user access to SQL is a good suggestion, however - for obvious reasons - you cant just remove user access since that would also disable the usage of NAV for those users.

This is a scenario you a facing is a scenario involving "the evil admin". Any advice given in such a scenario can be easily overcome as long as the evil admin has access (either physical or remote) to the machine (the server).

Furthermore, your challenge apperently falls into two categories - proving what happened and by who, and preventing it from happening again.

You should be able to turn on logging on the SQL server (search for trace flags using your favorite search engine) for certain operations - e.g. restore - but again, the evil admin might just disable/circumvict your attempts.

First thing would therefore be to remove the resellers access to the machine. This includes remote access etc.

Second make sure all users changes their password, and delete any "old" or "test" users. Explain to all users never to "lend" their password to anybody.


Good luck,