December 21, 2007 at 11:20 am
Here's the error msg I get when I attempt to open the "Database Diagram" folder for this db:
TITLE: Microsoft SQL Server Management Studio
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.
Near as I can tell the db owner is the same as for all the dbs in my server (including AdventureWorks) Info in the Files page of the Database Properties dialog box is the same as all the other. Help is not clear on how I can use the "ALTER AUTHORIZATION" statement to handle this.
One different thing about this db: It was restored from the backup of a SQL Server 2000 db that I developed some years ago.
Any Idea?
Joel
Takauma
December 21, 2007 at 11:27 am
If you restored from another location, then the owner may not exist on the new server. Remember - the owner is not just a name, it is also a SID which is unique on all servers. So, John Doe on one server isn't John Doe on another.
I suggest just running sp_changedbowner for the database giving you the problem. (sp_changedbowner 'sa') This command must be run in the database where you are changing the owner.
You can run this to find databases not owned by a specific login (in this case SA) and that are also NULL for the owner.
USE master
SELECT sd.[name] as [DBName], sl.[name] AS [Owner]
FROM master.dbo.sysdatabases sd
LEFT OUTER JOIN master.dbo.syslogins sl
ON sd.sid = sl.sid
WHERE LOWER(sl.[name]) <> 'sa'
OR sl.[name] IS NULL
-SQLBill
I'll guess that your database will be returned with NULL as the owner.
December 21, 2007 at 11:28 am
ALTER AUTHORIZATION ON DATABASE::[dbname] to [owner]
For Example
ALTER AUTHORIZATION ON DATABASE::adventureworks to sa
Check out BOL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2007 at 11:40 am
Thanks SQLBill:
I applied the following:
use splint -- "splint" is my errant db name
exec sp_changedbowner 'sa'
No change in behavior. Same error msg.
Should I restart the SQL Server?
Are Diagrams stored in dtproperties or sysdiagrams?
Takauma
December 21, 2007 at 11:42 am
Thanks Jack:
I applied the following:
use master
ALTER AUTHORIZATION ON DATABASE::SPLINT to sa -- "splint" is my errant db name
No change in behavior. Same error msg.
"Ckeck out BOL"?
Takauma
December 21, 2007 at 12:09 pm
Found someone else with this problem here:
http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic37118.aspx
THis post suggests making sure the database compatibility level is set to 9.0 (Sql Server 2005).
EXEC sp_dbcmptlevel 'database name', '90'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2007 at 2:02 pm
Applied:
EXEC sp_dbcmptlevel 'SPLINT'
returned '80'
Ah Hah!
Applied:
EXEC sp_dbcmptlevel 'SPLINT', '90'
That did the trick! Now I'm good to go.
Figured it had something do with that fact that it's legacy db.
Many Thanks!
Happy Holidays
Joel
Takauma
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply