ACC2000: How to Secure Database Diagrams in a Microsoft Access Project

ID: Q234343

The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).


SUMMARY

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.


MORE INFORMATION

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.

  1. Open the sample Access project, NorthwindCS.adp.


  2. 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.


  3. On the Server Logins tab, click Add.


  4. On the General tab, specify testUser in the Name text box.


  5. Click the Database Access tab and grant testUser permissions to access the NorthwindCS database. Click OK.


  6. In the SQL Server Security dialog box, click the Database Users tab.


  7. Select testUser, and click Edit.


  8. Click Permissions.


  9. Examine the list of objects in the database, and locate the dt_getobjwithprop and dt_getpropertiesbyid stored procedures.


  10. In the object list, DENY permissions to EXECUTE each stored procedure mentioned in step 9.


  11. Click Apply, click OK, and close the Database User Properties dialog box.


  12. Close the SQL Server Security dialog box, and then in the Database window, click Database Diagrams.


  13. 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.


  14. Examine and close the database diagram.


  15. On the File menu, click Connection.


  16. Modify the connection properties of your Access project so that you log on as testUser with no password. Click Test Connection.


  17. Click OK to return to the Database window.

    Note that the Relationships database diagram is no longer visible in the database diagram list.



REFERENCES

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.

Additional query words: 


Keywords          : kbdta AccessCS 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: July 9, 1999