How to: Set Up Database Diagram Designer (Visual Database Tools)
To use Database Diagram Designer, it must first be set up by a member of the db_owner role to control access to diagrams. For more information on how diagram ownership works, see Understanding Database Diagram Ownership (Visual Database Tools).
To set up database diagramming
From Object Explorer, expand a database node.
Expand the Database Diagrams node under the database connection.
Select Yes when prompted if you want to set up database diagramming.
Note
This will create the database diagram table, system stored procedures, and a system function on the SQL Server database.
Visual Studio 2005 will create the following objects on the instance of SQL Server:
- sysdiagrams table
- sp_alterdiagram stored procedure
- sp_creatediagram stored procedure
- sp_dropdiagram stored procedure
- sp_renamediagram stored procedure
- fn_diagramobjects function
- sp_helpdiagrams stored procedure
- sp_helpdiagramsdefinition stored procedure
- sp_upgraddiagrams stored procedure
Important
To install database diagram support in SQL Server Management Studio, the database must be in SQL Server 2005 database compatibility level. Database compatibility level can be reset after diagram support is installed. Use the following Transact-SQL statement to change the compatibility level of the database, and then retry the database diagram operation. EXEC sp_dbcmptlevel 'database_name', '90'; The installation of these support objects can fail on a database that has been attached or restored from another instance of SQL Server. This may occur when the database owner name (stored in the database) is not a valid login in the instance of SQL Server the database is being attached or restored to. Use the following Transact-SQL statement to change the database owner to a valid login in the instance of SQL Server, and then retry the database diagram operation. ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login
See Also
Tasks
How to: Upgrade Database Diagrams from Previous Editions (Visual Database Tools)
Concepts
Understanding Database Diagram Ownership (Visual Database Tools)
Other Resources
sp_dbcmptlevel (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)