| Author |
Topic |
|
kkrishna
Starting Member
23 Posts |
 Posted - 2010-05-13 : 18:04:26
|
| We have a SQL (2005) database and a front end Access application linked to the database. The data can be viewed but cannot be changed.We view (only view and NOT attempting to link) the fields for various tables to see the possibility of creating a query for a report. Once we determine what we need, the IT Admin will write the SQL query for us and produce a report.Now the database admin says that even viewing the table fields in the Relationships menu of the Access is prohibited as it can cause damage to the SQL database. To know what fields are there in the tables, we have to ask the IT Admin.We do not mind doing that, if an act of just viewing the table fields in the table relationship can really cause damage to the database. We are not database specialists but we have experience with MS Office (including Access) for several years. It is surprising that we can cause damage to the database with our limited action.Can the experienced database specialists offer their views on this subject?Thanks in advance |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-13 : 21:59:07
|
| If all you have is SELECT permissions on the tables you are linking to, then no - you can't damage the SQL Server database or tables. However, when you select data from the system - you can (and probably will) cause performance issues on the server.Access has a tendency to perform it's selects row by row - causing blocking on the tables until all of the data has been selected. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-14 : 04:36:30
|
| There is nothing that you can do via queries that will corrupt a SQL database. Corruption is either bug in the product, damaged/faulty memory on the server or a misbehaving IO subsystem--Gail ShawSQL Server MVP |
|
|
|
kkrishna
Starting Member
23 Posts |
Posted - 2010-05-14 : 08:40:11
|
| Thanks for the quick responses.We are neither writing SELECT queries nor linking the tables in the Relationships screen of Access. We select various tables to display their fields in the Relationships screen. From the primary and secondary keys of the tables, we want to draw a plan what data we can get in a query. Then we go to IT and tell them what we need with the help of the info we gathered. They write the SQL query (or link the table fields) to create a query which will be the the basis for a report.We are now told that the selection of tables in the Relationships screen itself can cause damage to the database.That is what baffles us. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-14 : 09:41:29
|
| Total garbage. The only way to get data out of a SQL database is to query the tables. Whether you are writing select statements or another app (like Access) is, is irrelevent. You cannot, by querying a database, cause any form of corruption.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-14 : 09:49:26
|
I think your IT folk are talking bollocks! Unless there is a performance issue that is worrying them, or something like that (not apparent from your description, but may have lost something in translation). I doubt that is the case with Access querying just the schema of the SQL database, but Access might be doing some mega-query to get the data it wants in order to display what you are seeing.Additionally, putting myself in their shoes, I would be worried that non-technical people had understood the "nuance" of a column when defining their own reports. Sure there may be a column called OpenOrClosed but it may only mean "Open", in human parlance, if the Disabled field is also set to False - that sort of "nuance" thing.Either way, you should only have Select permissions (and if not Strike 2 for your IT folk!!) and with that in place you cannot modify the database at all.If they have given you SysAdmin access (either by accident or because they can't work out how to give you connectivity any other way) then ... Strike 3  Pls report back, I'm on the edge of my seat! |
|
|
|
kkrishna
Starting Member
23 Posts |
Posted - 2010-05-14 : 12:23:40
|
| Thanks Jeff for the response.The fact that we are able to view the list of tables, queries etc in the Access front end means we have permission for SELECT queries.Or am I wrong?We do not have SysAdmin permissions.However the point is we are not writing any SELECT queries or creating queries by joining tables. We are only selecting table names from the drop-down list on the Relationships screen, expand the size of the figure to see all the field names.Regarding "nuance" of the field names, we seek the assistance of the IT staff anyway. If we can not make out the meaning of a field name, we have to ask the IT what it means. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-14 : 12:58:31
|
Yeah, you need SELECT permissions on the system tables that store the information about the Tables and Columns - that "meta data" can be queried just like any other data - I presume that's how Access is getting it (but its just possible its doing something else / different)Yup, I've understood that you aren't making any actual queries, just querying the database structure information. That should be lightweight (but I don't know how Access pulls that data, so its possible its more a serious data request - but - how often are you doing it? I doubt its in the realms of hundreds-of-times-an-hour - and thus nothing like the impact of real users accessing the database)If the IT Folk would be amenable to telling you why using Access to view the database structure is a risk that might shed some light on what their worry is. Absent that I'm of the opinion that the way they have described the problem to you (may not be the real reason etc.) is rubbish.But I've got my hat ready in case I have to eat it ...  |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-14 : 17:44:06
|
quote:
Originally posted by kkrishna
Thanks for the quick responses.We are neither writing SELECT queries nor linking the tables in the Relationships screen of Access. We select various tables to display their fields in the Relationships screen. From the primary and secondary keys of the tables, we want to draw a plan what data we can get in a query. Then we go to IT and tell them what we need with the help of the info we gathered. They write the SQL query (or link the table fields) to create a query which will be the the basis for a report.We are now told that the selection of tables in the Relationships screen itself can cause damage to the database.That is what baffles us.
If you are not linking to the tables, then how are the tables accessible in Access? You either have to link to the tables to make them available, or you are not using an Access database and instead are using an Access Data Project.An Access data project connects directly to SQL Server and does not use an mdb file. In this case, if you have the ability (permissions) to select the tables and pull them into the relationship diagram - then you can easily cause *damage* to the system. The damage could be caused by *accidentally* changing the relationships, adding columns to tables, removing columns, etc... |
|
|
|
kkrishna
Starting Member
23 Posts |
Posted - 2010-05-14 : 17:45:01
|
| Thanks,Kristen.We do not indulge in this exercise day in and day out. We have other jobs to do. We want to look at the table schema only when there is a need for additional information as a report. It could be once a month if not longer. |
|
|
|
kkrishna
Starting Member
23 Posts |
Posted - 2010-05-14 : 18:00:21
|
| Hello Jeff,Good point. My question is if a user is provided permission to access the table schema (I know that the data is read-only), can the user change the table structure? That means the user can add or delete columns for the existing tables - that is news to me. The user can join the tables to create queries - yes, I have seen other users doing that. I never tried it (and do not want to). But that is rather dangerous. As I understand that job can only be done by someone with Admin permissions. Where is the need to permit a user to view the table structures? |
|
|
|
kkrishna
Starting Member
23 Posts |
Posted - 2010-05-14 : 18:08:53
|
| If the information helps.The front end file I have has .accdb extension - Microsoft Access 2007. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-15 : 04:16:37
|
| I don't have enough knowledge of Access to know, but Jeff's point is a good one. However, you don't say that you are making relationships in Access, IIUC you are only viewing them. It is possible that even to View relationships Access may need more than SELECT permission to the database (i.e. on the assumption that your intent may be to then make modifications), and thus maybe IT have had to give you higher permissions - if that is the case then there is definitely a risk that through accident you change/delete a column, or a table relationship.Perhaps the answer is to ask IT if you only have SELECT (and if not to change your permission to SELECT only and see if you can do what you used to in Access, and if IT are then happy?).I still think that either IT are talking rubbish, or there is more to it than you have been able to describe. Either way I think you need IT to clarify what their concern is.You being able to see the database structure and posing a considered request for a report to IT has got to be less hassle for IT than them constantly having to give you lists of columns, surely? |
|
|
|
kkrishna
Starting Member
23 Posts |
Posted - 2010-05-15 : 09:13:30
|
| Hello Jeff and Kristen,Thanks for the response.I did some home work yesterday to check on the realities. I have a test database on the SQL Express 2008 at home. I created an Access front end for the database; now I have .accdb file (not .accde file) linking the tables with SQL database. I opened the table structure in the Relationships table, selected a field and tried to delete it. I could not. I navigated to the table on the left side of the panel and tried to delete it. I could not. I get the following message.“Table dbo_TestTable is a linked table whose design can’t be modified. If you want to add or remove fields or change their properties or data types, you must do so in the source database.Do you want to open it anyway?Yes No “ The SQL Express database is set for Windows Authentication. Interestingly from the Access front end, I can open the dbo_TestTable and change data in the table. I cannot do it in a production database, probably because my permissions for the data are Read-only.Coming back to the original question, what can go wrong in accessing the table structure in the Relationships screen? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-15 : 13:16:43
|
"Coming back to the original question, what can go wrong in accessing the table structure in the Relationships screen?"If you only have SELECT permissions at work then nothing.If they have given you "modify" access then you could (accidentally, whatever) remove a relationship between tables, drop columns, all sorts.Notwithstanding that: the "Linked Table" you have in Access appears to prevent you making those sort of changes anyway; but I don't think that is the issue - if you have anything other than SELECT permissions you could modify something (lets assume you had malicious intent, for example).Comes back to: your IT department should check that you only have SELECT permissions, and if not reduce your permissions to just SELECT (and then you should check if anything no longer works).And/or IT have some other issues that are worrying them (that they haven't told you / explained clearly to you).Or they don't know which way UP is  |
|
|
|
kkrishna
Starting Member
23 Posts |
Posted - 2010-05-20 : 10:35:23
|
| Hi Kristen and others,Thank you all for the responses.One more detail before we close this topic.From the Access front end, what is the difference between opening a table by the SQL query "SELECT * FROM blahblah" and opening the table by double-clicking on table name ( or click "Open" in datasheet view) on the left side navigation panel.Is one alternative more "harmful" to the table (and the database) than the other?Krishna |
|
|
|
|
Possible corruption of the database