Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 234343 - Last Review: January 26, 2005 - Revision: 2.1
ACC2000: How to Secure Database Diagrams in a Microsoft Access Project
This article was previously published under Q234343
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access project (.adp).
When you create a database diagram in a Microsoft Access project, it is not stored on SQL Server or Microsoft Data Engine (MSDE) as an object (as a table, a view, or a stored procedure would be).
Database diagrams are dynamically rendered, based on information gathered by a series of stored procedures in your user database. These stored procedures are normally hidden. Because database diagrams do not exist as true objects, the SQL Server Security Tools included in an Access project do not offer a way to directly modify their security permissions.
You can prevent users from viewing any database diagrams in a user database by denying EXECUTE permissions on the
dt_getobjwithprop and
dt_getpropertiesbyid stored procedures. However, there is no way to selectively allow users access to some database diagrams and deny access to others.
To secure database diagrams in a Microsoft Access project, follow these steps. The steps create a test SQL Server logon and user account, and then set the permissions of the new user account so that it may not access database diagrams.
- Open the sample Access project, NorthwindCS.adp.
- On the Tools menu, click Security, and then click Database Security.
NOTE: To complete the next steps, you must use a logon and user account with System Administrator privileges when connecting NorthwindsCS.adp to its back-end data source. - On the Server Logins tab, click Add.
- On the General tab, specify testUser in the Name text box.
- Click the Database Access tab and grant testUser permissions to access the NorthwindCS database. Click OK.
- In the SQL Server Security dialog box, click the Database Users tab.
- Select testUser, and click Edit.
- Click Permissions.
- Examine the list of objects in the database, and locate the dt_getobjwithprop and dt_getpropertiesbyid stored procedures.
- In the object list, DENY permissions to EXECUTE each stored procedure mentioned in step 9.
- Click Apply, click OK, and close the Database User Properties dialog box.
- Close the SQL Server Security dialog box, and then in the Database window, click Database Diagrams.
- Double-click the database diagram named Relationships. Because you are currently connected to the SQL Server or MSDE as a system administrator, it should open.
- Examine and close the database diagram.
- On the File menu, click Connection.
- Modify the connection properties of your Access project so that you log on as testUser with no password. Click Test Connection.
- Click OK to return to the Database window.
Note that the Relationships database diagram is no longer visible in the database diagram list.
For more information about database diagrams, click
Microsoft Access Help on the
Help menu, type
Database Diagrams in the Office Assistant or
the Answer Wizard, and then click
Search to view the topics
returned.
APPLIES TO
- Microsoft Access 2000 Standard Edition
| kbhowto kbclientserver KB234343 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate