Friday, December 3, 2010

How to Resolve issue for Database diagram support objects cannot be installed

Many times you find situation when you need to design database using Database Diagram. When you try to open "Database Digram" node from SQL management studio you find following error.




You can resolve it by following script.

EXEC sp_dbcmptlevel 'DummyDB', '90';
go
ALTER AUTHORIZATION ON DATABASE::BIDashBoard TO "Domain\UserName"
go
use [DummyDB]
go
EXECUTE AS USER = N'dbo' REVERT
go

What above solution does ?
It will first make your compatibility level to 90. You can determine the level by below script.

select compatibility_level
from sys.databases
where name = 'DummyDB'

Here are list of compatibility level and MSSQL version mapping,

* 60 = MSSQL 6
* 65 = MSSQL 6.5
* 70 = MSSQL 7
* 80 = MSSQL 2000
* 90 = MSSQL 2005
* 100 = MSSQL 2008

Other thing is it will set authorization to the user name provided.
Right Click on Database
Go to properties
Click Files
you will end up as shown below.



Before running this script owner tab will be blank.
You can run above script or either go to the click button near by textbox and locate your user.

That's it.

Thanks,
Ashish Chotalia

No comments:

Post a Comment