Giving direct access rights to main database issues

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.

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.

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?

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

Designing reliable database and reporting systems is not trivial.

Thanks Megan. Yes it's OLTP. users already have granted access to OLTP tables.(this is ERP Database).

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

thanks

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.