VS 2005 - Server Explorer - Diagrams - Invalid DBO User

[Tibor Karaszi]

Orphaned database owner (check the SID for dbo user in the database and that such exists in master
for the logins).?

 

[Jay Douglas]

When attempting to edit diagrams in Server Explorer from Visual Studio 2005
I receive the following error:

This database does not have a valid dbo user or you do not have permissions
to impersonate the dbo user, so database diagramming is not available.
Ensure the dbo account is valid and ensure that you have impersonate
permission on the dbo account.

I am not logged in w/ a user created user called MyUser. I set the default
schema to dbo for MyUser on MyDatabase. I also placed MyUser has db_owner
on MyDatabase.

What am I missing?

Thanks,

 

[Jay Douglas]

Tibor,

I'm pretty sure that you are correct about the orphaned user as this
deployment of the database was a backup from a different database.

However, I dropped the user mapping for MyUser to MyDatabase and also
removed the login. I recreated the login and user mapping for MyUser with
dbo schema and db_owner role and I'm receiving the same error......

Any further help is very appreciated. I can't find any answers in Google.

Thx again

 

[Gail Erickson [MS]]

What's the compatibility mode of the database? If it's 80 or lower, you
might try setting the compat mode to 90 and retrying. I read a post
yesterday that indicated this might solve the problem. At least it wouldn't
hurt to try - you can always set it back to the orginal compatibility level.

 

[Jay Douglas]

Okay, I fixed it, and I'm not too sure how. But here is what I did:

I opened the properties on the database in Management Studio, clicked on
permissions, closed the window. And magically everything works.

I'm sure my deployment model is flawed. In SQL 2000 I created a script that
did a drop/add user after restoring a database from a different db server.
I'm sure I'll need to do the same for SQL 2005.

Thx for your time.

 

[Guest]

YES I am having the same problam and by changing the compatibility level it
fixes the problem

The only problem with that is that my customers have not yet updated to SQL
2005 and I need the database to be in SQL 2000 compatibility level, and by
you saying that "you can always set it back to the orginal compatibility
level"

Do you mean that I will be able to backup and restore this database back to
my customer after that?

And what about the database diagrams will thay get deleted?

Thanks

What's the compatibility mode of the database? If it's 80 or lower, you
might try setting the compat mode to 90 and retrying. I read a post
yesterday that indicated this might solve the problem. At least it wouldn't
hurt to try - you can always set it back to the orginal compatibility level.

 

[Gail Erickson [MS]]

by you saying that "you can always set it back to the orginal
compatibility

level"
Do you mean that I will be able to backup and restore this database back
to
my customer after that?

What I meant was that you could set the database compatbility level to 90,
use the diagram tool and then reset the compatibility level back to 80. This
is documented in the readme file that installs with SQL Server 2005. See
section 4.8.1 Considerations for Installing Database Diagram Support.

The only problem with that is that my customers have not yet updated to
SQL
2005 and I need the database to be in SQL 2000 compatibility level, and by
you saying that "you can always set it back to the orginal compatibility
level"

If your requirement is to use the database on an instance of SQL Server
2000, then you should use Enterprise Manager to work on the diagrams. Even
if you set the database compatbility mode back to 80, you cannot backup a
SQL 2005 database and then restore it on an instance of SQL Server 2000.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

YES I am having the same problam and by changing the compatibility level
it
fixes the problem

The only problem with that is that my customers have not yet updated to
SQL
2005 and I need the database to be in SQL 2000 compatibility level, and by
you saying that "you can always set it back to the orginal compatibility
level"

Do you mean that I will be able to backup and restore this database back
to
my customer after that?

And what about the database diagrams will thay get deleted?

Thanks

What's the compatibility mode of the database? If it's 80 or lower, you
might try setting the compat mode to 90 and retrying. I read a post
yesterday that indicated this might solve the problem. At least it
wouldn't
hurt to try - you can always set it back to the orginal compatibility
level.

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no
rights

Tibor,

I'm pretty sure that you are correct about the orphaned user as this
deployment of the database was a backup from a different database.

However, I dropped the user mapping for MyUser to MyDatabase and also
removed the login. I recreated the login and user mapping for MyUser
with
dbo schema and db_owner role and I'm receiving the same error......

Any further help is very appreciated. I can't find any answers in
Google.

Thx again

--
Jay Douglas
http://www.jaydouglas.com


"Tibor Karaszi" <[email protected]>
wrote
in message Orphaned database owner (check the SID for dbo user in the database
and
that such exists in master for the logins).?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


When attempting to edit diagrams in Server Explorer from Visual
Studio
2005 I receive the following error:

This database does not have a valid dbo user or you do not have
permissions to impersonate the dbo user, so database diagramming is
not
available. Ensure the dbo account is valid and ensure that you have
impersonate permission on the dbo account.

I am not logged in w/ a user created user called MyUser. I set the
default schema to dbo for MyUser on MyDatabase. I also placed MyUser
has db_owner on MyDatabase.

What am I missing?

Thanks,

 

[Guest]

Thanks
--
Lazer
M. E. Computers & Programming
www.mecomputers.com

by you saying that "you can always set it back to the orginal compatibility
level"
Do you mean that I will be able to backup and restore this database back
to
my customer after that?

What I meant was that you could set the database compatbility level to 90,
use the diagram tool and then reset the compatibility level back to 80. This
is documented in the readme file that installs with SQL Server 2005. See
section 4.8.1 Considerations for Installing Database Diagram Support.

The only problem with that is that my customers have not yet updated to
SQL
2005 and I need the database to be in SQL 2000 compatibility level, and by
you saying that "you can always set it back to the orginal compatibility
level"

If your requirement is to use the database on an instance of SQL Server
2000, then you should use Enterprise Manager to work on the diagrams. Even
if you set the database compatbility mode back to 80, you cannot backup a
SQL 2005 database and then restore it on an instance of SQL Server 2000.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

YES I am having the same problam and by changing the compatibility level
it
fixes the problem

The only problem with that is that my customers have not yet updated to
SQL
2005 and I need the database to be in SQL 2000 compatibility level, and by
you saying that "you can always set it back to the orginal compatibility
level"

Do you mean that I will be able to backup and restore this database back
to
my customer after that?

And what about the database diagrams will thay get deleted?

Thanks

What's the compatibility mode of the database? If it's 80 or lower, you
might try setting the compat mode to 90 and retrying. I read a post
yesterday that indicated this might solve the problem. At least it
wouldn't
hurt to try - you can always set it back to the orginal compatibility
level.

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no
rights

Tibor,

I'm pretty sure that you are correct about the orphaned user as this
deployment of the database was a backup from a different database.

However, I dropped the user mapping for MyUser to MyDatabase and also
removed the login. I recreated the login and user mapping for MyUser
with
dbo schema and db_owner role and I'm receiving the same error......

Any further help is very appreciated. I can't find any answers in
Google.

Thx again

--
Jay Douglas
http://www.jaydouglas.com


"Tibor Karaszi" <[email protected]>
wrote
in message Orphaned database owner (check the SID for dbo user in the database
and
that such exists in master for the logins).?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


When attempting to edit diagrams in Server Explorer from Visual
Studio
2005 I receive the following error:

This database does not have a valid dbo user or you do not have
permissions to impersonate the dbo user, so database diagramming is
not
available. Ensure the dbo account is valid and ensure that you have
impersonate permission on the dbo account.

I am not logged in w/ a user created user called MyUser. I set the
default schema to dbo for MyUser on MyDatabase. I also placed MyUser
has db_owner on MyDatabase.

What am I missing?

Thanks,

 

[michal.kreslik]

Hello,

actually, the problem lies in something different:

• in the "Server Explorer" window, click once on your SQL database file name (for example, "MySQLdatabase.mdf")
• now have a look at the "Properties" window, row "Owner". It should read something like "MyPCName\John User"
• if you are not logged in as "John User", then this is your problem
• if you're logged in as "John User", right click your "My Computer" icon and select "Properties"
• if your "Full computer name" in the "Properties" > tab "Computer Name" window is NOT "MyPCName" (as in the above example), then this is your problem

Michal Kreslik
michal.kreslik(at)kreslik.com