Another example of useful error messages! If you create a SQL 2005 DB and then find that you can't create a DB diagram because of an authorisation problem, I have found that it might be one of the following two:
- Set the compatibility to SQL 2005 (90) by right-clicking on the database node in SQL Server Management Studio, choose 'Options' from the list on the left and then select SQL 2005 from the 'Compatibility' combo at the top
If that doesn't work, or it was already set to SQL 2005 (the default), then try the following:
- Open a new query window and execute the following script:
EXEC sp_dbcmptlevel '', '90';
ALTER AUTHORIZATION ON DATABASE:: TO [valid_login]
This sets the owner to the valid login specified at the end of the script and now when you click on diagrams, it should automatically install diagramming support for the DB.
Hope that helps!!