如何摆脱 SQL Server 2000 中的扩展表属性？
我正在重新铸造
我所拥有的：SQL Server 2000 上的数据库中的表。我使用 Access 2007、SQL Server Management Studio 2005 以及偶尔使用 SQL 2000 Enterprise Manager 查看/编辑表。
我所做的：我尝试将数据库从 SQL Server 2000 复制到 SQL Server 2005 的本地实例。复制过程没有错误。当我尝试在 Access 2007 中查看复制的数据库中的表时，出现了异常。
我发现：抛出异常的表具有与其关联的扩展属性。我追溯到说“是”保存对 Access 中表布局的更改。
我试图解决这个问题：我尝试通过 SQL Server 2005 Management Studio 删除扩展属性并重新复制数据库，但这并没有解决问题。在编写表格脚本后，我发现扩展属性实际上并没有在表格上消失。
现在回答我的问题：
我使用企业管理器找到了位于数据库中的一个名为sysproperties 表的表。 这可能是一个未记录的表（叹气），但看起来它具有扩展的属性信息，这让我很头疼。我尝试修改另一个表的布局，以查看是否有任何记录添加到 sysproperties 表中，但看起来答案是否定的。
有人对这个问题有经验吗？仅删除此表中的条目是否安全？我认为我所做的大部分“布局更改”都是调整 Access 中列的大小，因此如果这就是存储在那里的全部内容，我可以接受。
此外，我在企业管理器中查找了扩展表属性，但它们不像在 SSMS 2005 中那样易于访问。
提前致谢！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你没有说你的Access前端是MDB还是ACCDB，但如果是前者，为什么不在TableDef上设置不允许用户保存设计更改的权限呢？您需要检查这一点，但我认为您想要在前端的表格链接上删除“修改设计”和“管理”属性。
如果是 ACCDB，也许修复它的唯一方法是重新创建链接表。
不过，我确实质疑为什么您允许用户直接编辑表格。您应该只允许他们访问表单和报告中的数据（表单可以是数据表，因此您可以复制表数据库表的所有便捷功能，而无需使用表本身）。
另外，您的用户似乎必须对 SQL Server 表拥有高于其应有权限的权限。我不知道这些属性如何存储在 SQL Server 中的详细信息（我的 SQL Server 使用旧版本），但应该对这些对象分配组权限，以禁止将这些属性保存到服务器。
尽管如此，您通过让用户直接访问链接表而导致了自己的问题。解决这个问题，您的问题就会消失，而无需担心 Access 或 SQL Server 中表的用户权限。
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.