How to get rid of extended table properties in SQL Server 2000?

Question

I'm re-casting a question I asked earlier now that I have an idea of the cause of the problem I'm seeing.

What I have: Tables in a DB on SQL Server 2000. I view/edit the tables with Access 2007, with SQL Server Management Studio 2005, and occasionally with SQL 2000 Enterprise Manager.

What I did: I tried to copy the DB from SQL Server 2000 to my local instance of SQL Server 2005. The copy occurred without errors. When I tried to view tables in the copied DB in Access 2007, I got exceptions thrown at me.

What I found: The tables that were throwing the exceptions had extended properties tied to them. This I traced back to saying "yes" to saving changes to the table layout in Access.

What I tried to do to fix this: I tried removing the extended properties through SQL Server 2005 Management Studio and re-copying the DB, but this didn't fix the problem. Upon scripting the tables I saw that the extended properties really didn't go away on the tables.

Now to my question:

I tracked down a table using Enterprise Manager called the sysproperties table that lies in my DB. This might be an undocumented table (sigh) but it looks like it has the extended property information that is causing me all of the headaches. I tried modifying the layout of another table to see if any records were added to the sysproperties table, but it looked like the answer was no.

Anyone have experience with this issue? Is it safe just to remove the entries in this table? I think most of the "layout changes" I did were adjusting the size of columns in Access, so if that's all that's being stored there, I can live with that.

What's more, I looked for the extended table properties in Enterprise Manager, and they weren't readily accessible like they are from within SSMS 2005.

Thanks in advance!

Answer 1

You don't say if your Access front is MDB or ACCDB, but if it's the former, why not set permissions on the TableDef that disallows users from saving design changes? You'll need to check this, but I think it's the MODIFY DESIGN and ADMINISTER properties that you'd want to remove on the table links in your front end.

If it's ACCDB, perhaps the only way to fix it is to recreate the linked tables.

I do question, though, why you are allowing your users to edit tables directly. You should be giving them access to the data only in forms and reports (forms can be datasheets so you replicate all the handy features of table databasheets without needing to use the tables themselves).

Also, it seems your users must have privileges on the SQL Server tables that are above what they should have. I don't know the details of how those properties are stored in SQL Server (my SQL Server work is with older versions), but there should be group permissions assignable on those objects that would prohibit the saving of those properties to the server.

All that said, you're causing your own problem by giving users direct access to the linked tables. Fix that and your problem goes away without needing to worry about user permissions on tables either in Access or SQL Server.