February 5, 2009 at 2:13 am
I have a user who wants to connect Excel using ODBC directly to our accounting system to create their own reports.
I have advised that instead, they use the reporting tools that come with the system or we use SSRS which is supported by the vendor.
Their reponse is along the lines of that they know what they are doing and to give them SA access to the database.
Now, if I gave them read-only access, could there be any issues or is there a real reason why using Excel to produce reports is a really bad idea?
Their SQL skills are pretty poor but they are the sort of person who knows a little bit more than everyone else so they are percieved to be the expert. My views are disregarded .
My main issue is that a compex Excel spreadsheet is difficult to suport and maintain - what if the vendor changes the DB schema? At least if we use the pre-defined views they are supported by the vendor and they help simplify the SQL.
Any thoughts?
February 6, 2009 at 6:40 am
That is a really bad idea.
First of all, only the sysadmin should have sa access.
Second, most accounting systems are very complex behind the scenes, and technical documentation is usually sparse. Unless you've had formal training in the product, then your reports would be wrong.
Finally, unless your user is a company executive there is certainly sensitive data that they have no business viewing.
February 6, 2009 at 7:02 am
Thanks.
They are head of Finance so that's not really an issue. The reports they are producing are their responsibility.
The structure of the system and where things are I can agree with.
What about connecting Excel to SQL? Is that generally a good idea? What can go wrong?
I have had the SA discussion and they just don't understand why it is an issue and insist, even after I've granted them read access using an NT account in using SA.
I suspect, given the fact that they don't understand why they shouldn't use SA, they won't be too fussed about any concerns over connecting Excel to SQL.
I *could*, rename the actual SA account and create another one called 'SA' with read-only access....
February 6, 2009 at 8:01 am
WHY do they think they need SA access - is there any functionality it would give them above and beyond what the NT account gives them (in terms of the spreadsheet issue). I sure wouldn't want them having SA authorization, especially if they know just a little SQL; that's just way too dangerous.
What's the ROI (return on investment) for what they're doing? 😉
Steph Brown
February 6, 2009 at 8:16 am
funkyd (2/5/2009)
I have advised that instead, they use the reporting tools that come with the system or we use SSRS which is supported by the vendor.
One more thing - if the vendor does not support what you are doing you could wind up in a big mess if something goes wrong.
February 6, 2009 at 8:25 am
Stephanie J Brown (2/6/2009)
WHY do they think they need SA access
I think the answer is that they don't know what they are doing.
It's a tough position, it's their system and they are responsible for it and I work for them.
Clearly, creating another user with the rights they need but also providing the SA password because they are the boss hasn't worked.
I really don't think there is much I can do here other than accept that sometimes the boss is an idiot!
February 6, 2009 at 8:31 am
Ugh. Unless the person is a domain expert AND trained on the underlying application schema, they are risking getting wrong/incomplete/misleading data, and making incorrect assumptions. Many vendors supply some template reports or views which be used. The template reports' queries could be SLIGHTLY modified and reformat/add data. Reporting Views often label the data somewhat more clearly, and make sure any in-obvious calculations and adjustments are made.
If the user is only semi-cluefull, as you suggest, This has boom-boom flags all over the place.
Of course he need sa access. How else can he tweak the data to get "results" he needs! :Whistling:
/sarcasm
February 7, 2009 at 8:21 am
Talk to the user's manager about the request, the reasons why it's a bad idea, the alternatives, and any applicable legal/standards requirements. If they still want you to proceed then have them request it in writing with an acknowledgment of your concerns.
February 7, 2009 at 8:58 am
If the user is the head of Finance then the politics can quickly get out of control. I would suggest that you talk to your boss & explain your concerns. If they are as level-headed as you are, then they will talk to their boss, who will talk to their boss etc. up the chain of command until it reaches the head of IT.
February 8, 2009 at 1:56 pm
funkyd (2/6/2009)
They are head of Finance so that's not really an issue.
[font="Verdana"]Tell that to the head of HR.
Some thoughts: you can create a login and grant it the data reader role. So there's no need to be handing out SA or any other administrative role.
Secondly, having Excel hooked up to a database directly can be powerful. But for all of the reasons you've already seen mentioned here, it should not be the production database. Create a reporting database expressly for this purpose.
From there, we lead on to all sorts of interesting discussions around appropriate tools and serviced reporting in a BI/DW environment. But at least as a "get the Finance head off my back", giving them a secured copy of the database with just the information they need against which they can run Excel is probably an okay interim step.[/font]
February 9, 2009 at 9:37 am
Yes, give them a replica they can torture daily, since you could automate its replacement every night. Create a user account that sounds close enuf - would he know the difference?
I would alternatively dump exports nightly, once he defines what he wants (another potential issue?)
Definitely do not hook into that live database - most of our vendors will invalidate our support contracts for doing such a thing. not worth the risk.
February 9, 2009 at 12:52 pm
I've yet to see either users of Excel, Crystal, Reporting Services, Cognos, or any of the myriad of other reporting tools, create optimized queries just by using the reporting tools, so watching out for performance hits will be necessary. As long as they don't need any hand holding, read only access by any reporting tool should be fine.
As for granting the SA password to anyone in Finance, if your organization is large enough, mention that this request would put compliance in SOX Section 404 in doubt. Accountants being able to change the numbers behind the scenes is really frowned upon. That may get the Finance head to rethink the request.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply