We help IT Professionals succeed at work.

Giving  direct access rights to main database  issues

155 Views
Last Modified: 2017-12-24
Hi,
I need to know if i give users to give read only direct access main database create reports (using Excel -odbc,OLEDB), what kind of issue we will get.
Our server :20GB RAM ,Database : Sql server 2008R2
Users around :300
Comment
Watch Question

Megan BrooksSQL Server Consultant

Commented:
If you are providing access to a specific set of reporting tables, the main issue I can think of is the added complication of having to configure and support client-server connections for each user (it brings back memories). If users also issue ad hoc queries through their connections, that can be trouble (imagine what happens when somebody cross joins two enormous tables, or holds a transaction open for hours, or... - and these are actual examples).

If you grant access to your main OLTP tables, that is likely to be trouble from both a performance and a support standpoint. Locking, needing support for figuring out joins, change control.

Of course it depends on how many users, how large a database, how well the database is built, and so on.
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Will they be able to create their own queries?
If it's only for running existing queries I don't see a big issue since the queries should be tested before.
But if they are allowed to create their own queries they can perform very slow running queries that will create locks and maybe an high usage of the server resources.

Author

Commented:
yes.Many thanks , at the moment most of users just drag and dropping creating own queries using Excel. so i need to stop this. because database getting poor performance. so i need to give very valid and professional answer for the some managers,who are not know about IT. so I'm planing introduced Dataware house.

do you know what kind of locks will generate for this issue. recently i got some dead locks. i think it's possible Dirty read and may Exclusive locks?
Megan BrooksSQL Server Consultant

Commented:
Are you granting access to your OLTP tables? Don't!

Designing reliable database and reporting systems is not trivial.

Author

Commented:
Thanks Megan. Yes it's OLTP. users already have granted access to OLTP tables.(this is ERP Database).
SQL Server Consultant
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok thanks, as i said i will create Data warehouse.

Author

Commented:
thanks
Megan BrooksSQL Server Consultant

Commented:
You can develop a data mart for information that isn't being provided by the ERP vendor. You will need to figure out when you can run ETL, presumably during off hours, if you have off hours.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.