Understanding Database Diagram Ownership (Visual Database Tools)
To use Database Diagram Designer it must first be set up by a member of the db_owner role (a role of Microsoft SQL Server databases) to control access to diagrams. Each diagram has one and only one owner, the user who created it. For more information on setting up diagramming see How to: Set Up Database Diagram Designer (Visual Database Tools).
Some points to keep in mind about diagram ownership:
- Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.
- Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.
- If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.
Important
To install database diagram support in SQL Server Management Studio, the database must be in SQL Server 2005 database compatibility level. If the databases are not in 90 compatibility level, the following error occurs when you attempt to install diagram support: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects. 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: Set Up Database Diagram Designer (Visual Database Tools)
Other Resources
Working with Database Diagrams (Visual Database Tools)