Data Access and update from other applications

[btamulis]

Greetings -
Strange situation with one of my clients that I wanted to get some feedback on.
Somehow the IV00102 table (item qtys) has been significantly modified - they lost - BUYERID/item assignments, they lost purchasing and manufacturing leadtimes and they lost the make/buy codes. Basically, I am wondering if its possible to write back to a GPV8 - sql table from access or excel?
The client has several power users that have sa password and the ability to connect using access, excel, etc. In access can a user inadvertently update and write back to the table?
Or does the IV00102 only updated through application or query analzer?
Any thoughts would be appreciated.

Bron Tamulis, CPIM
Great Plains Mfg Consultant

 

[Alcar]

The client has several power users that have sa password and the ability to connect using access, excel, etc. In access can a user inadvertently update and write back to the table?

absolutely and it's really scary that most users have the sa password...

I would first suggest to change the sa password to something else (remember to change the password in the properties of the SQL Services as well)

Then I would create different user/application password with rights to read the different tables (not all of them!)

Lastly, if they did a good job of maintaining the database servers, they should have stored somewhere the database backups and transaction logs. With those you might be able to retrace when and what got deleted from the IV00102 table..

I have purchased a great program called SQL Log Rescue that allows me to go 'back in time' with the transaction logs and UNDO one single transaction ... without restoring everything back to that point...

Lastly you can always import data to a GP table, mainly with Integration Manager... Doing it directly via a SQL Transaction script you never know what triggers you might... well.. 'trigger'...
Always make a full backup of both Dynamics and Company dbs before applying any SQL-T changes...


Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[btamulis]

Thanks for your reply. I agree with your assessment.
Do you think anyone inadvertently changed data via outside application?
My question is a simple yes or no question - can a user modify SQL data in a GP table by way of Access or Excel?

Granted - client is absolutely crazy to allow certain users the ability to connect via ODBC using sa password.

We are attempting to explain how 11,000 plus records were modified. One question is whether someone used access or excel....another question would be whether a routine or utility could have made changes.....another would be if someone could have inadvertently restored from an old backup?

Thanks for the tip on SQL Log rescue - was it very expensive and will I be able to find it easily on the web?

Bron Tamulis, CPIM
Great Plains Mfg Consultant

 

[Alcar]

My question is a simple yes or no question - can a user modify SQL data in a GP table by way of Access or Excel?

When the user holds the sa password and knows some SQL scripting.. then they can modify all the data in the GP server by Access SQL queries and linked tables or Excel VBA scripts that run SQL scripts against the db.

One question is whether someone used access or excel....

This is going to be hard to figure out if not impossible. One way to find out is if they can find the access mdb file or the excel spreadsheet file that contains code that might affect GP data.
The other way is if they ever had SQL Performance Monitor running on a spare machine that logged all user access to GP (we have one). That can pull what program initiated the access to the SQL server...

another question would be whether a routine or utility could have made changes

Absolutely. If the routine was written to do so, it would without a problem when running under sa credentials.

another would be if someone could have inadvertently restored from an old backup?

Unless there are other changes on all the other tables, then I doubt it. The Restore would bring the ENTIRE db back to that place in time, and not only 1 table out of 100+

Thanks for the tip on SQL Log rescue - was it very expensive and will I be able to find it easily on the web?

You are welcome. Not as expensive as other products available out there. Within the couple hundreds if I remember correctly, instead of the thousands... Easy to find and you can download a 30 day trial that will only work against a local SQL server instance.. the full version works across the network.

We purchased it when a user accidently deleted an entire employee class (why would GP even allow you to do that), just a few days before payroll. With that program I was able to find out that the changes where done on a Saturday Afternoon, just before our weekly full backup.
I found the delete statement, and the user that ran it. The program automatically created the SQL script that would 'UNDO' that action and ran it via Query Analyzer. It took about 30 minutes to resume operations =) Plus.. all the work done so far that Monday, was still there =)
Short of amazing




Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[dbigelow]

Access allows you to directly write to the SQL tables if you know the sa password - if I create the access database with links to the GP tables usually I run a macro which creates a table or snapshot of the information as of when they open the access database - this allows them "real time" data but creates one more layer between them and the data - of course if they know access they could just open the tables and directly write to the SQL tables - but most my users are in need of very custom reports with lots of strange table combinations and are not "power users" of access.

Thank you

Dan Bigelow

 

[jazgeek]

yes, they can easier in access than in excel

-----------
and they wonder why they call it Great Pains!

jaz