Saturday, November 17, 2007

Common SQL Server 2005 Diagram Suport Errors

Here's a scenario, you're trying to create a database diagram and you're presented with the following error:

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.

There's two popular causes for this problem.

1) The database doesn't have a proper owner. This often happens if you're restoring the database from another SQL Server instance. To check the owner:
  • Right click on the database and click on properties. Click on the files group and make sure the owner property is set to a valid login. If you're lazy you can always set it to sa.

  • You can also do this T-SQL if you want to script it. The syntax looks like:
    ALTER AUTHORIZATION ON DATABASE::[databaseName] TO [owner]
2) Another trick you might try if the database does have a valid owner is setting the compatibility level to SQL Server 2005 (90). To change the compatibility level:
  • Right click on the database and click on properties. Under the options group set the Compatibility Level to SQL Server 2005 (90).

Well that's it folks. Hope it saves you a minute or two.

Best,
Tyler

No comments: