Problem in accessing the database(Sqlserver 2005) from client machine

Hi

We have upgraded sql server 2000 to sqlserver 2005 on the development server.

To access the database server, we installed the sql server management studio on the client PC with Admin Account. The .Net framework and sqlserver management studio were installed and I am able to access the database from the client PC using Admin Login. Then we logged off and tried to login as user account on the client PC. From the user account login we are unable to open the sql server management studio. The error is as follows

“The proper type library could not be found in system registry”

The client PC configuration : windows 2000 Professional ( sp4)

Regards,

K.S.Subba Rao

Hi K.S. Subba,

What is probably happening is the non-Admin user that you are logging on with does not have permissions to read the appropriate type library informations for SQL-specific COM components from registry. The fastest approach you can take to troubleshoot that is to use RegMon from http://www.microsoft.com/technet/sysinternals/SystemInformation/Regmon.mspx, find out which keys are "Access Denied" or "Not Found" and when logged as Admin, grant the permissions to those to the non-Admin user. As a lazy alternative you could try to grant permissions to the user to HKEY_LOCAL_MACHINE\SOFTWARE\Classes and all the subcomponents, but that might expose a security risk.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem creating diagrams

I just upgraded an existing sql server 2000 installation with the RTM sql server 2005 (developer). I went to work on a database that I've been working on for a couple of weeks before the upgrade, and wanted to create a database diagram. However, when I click on the "Database Diagrams" tree node, I get this message:
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.

BUTTONS:
OK

The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.
Any ideas what's going on?
brian

I haven't been able to reproduce a case where setting the database owner didn't solve the problem.

I'm suspecting this has something to do with the upgrade process. When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode.

As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram? (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...) You can set the database back to SQL Server 2000 compatibility after this experiment.

Please let me know if this makes any difference.

|||Hi,

I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...

I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.

Any hints would be highly appreciated.

Thanks,
Florin
|||I've filed a defect report for this issue.

Just to make sure I'm understanding you correctly:

1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
2) Your database is in SQL Server 2005 compatibility mode
3) Your database is read/write
4) Your database is owned by a valid login
5) You are logged in as an administrator
6) You are allowing Management Studio to try to install the diagram support objects
7) You don't get any error messages
8) You still can't create diagrams

I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected.

As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database?|||I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
1. Removed all schema's and logins associated with my login in each database
2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
3. Gave myself Sysadmin Role in samefolder under Roles
4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.

And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.

I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database to a User of dbo and default Schema of dbo.

Hope this helps. - Jeff|||Hi,

Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
I hope that helps in narrowing down the problem.

Thank you,
Florin|||

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

|||

Thank you very much, Steven,

For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.

Michelle.

|||

Thanks Steve,

I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..

thank u very much once again

Sree

|||

I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.

This fixed it;

1) Switch to compatibility mode 9.

2) Remove my old diagrams.

3) Switch back to compatibility mode 8.

I also ran this command.

ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]

|||

I'm having a similar problem with SQL Server Express in Visual Studio.

The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.

If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.

You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?

Thanks,

rich

|||

I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1

rich

|||

Hello,

I had the same problem without upgrading a database.

I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.

Even if you change then the Compatibility level back to SQL Server 2000 its ok.

Thanks.

Alex.

|||

Hi Michelle,

Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.

Thanks,

Ron

E-Mail: tdi358@.yahoo.com

|||

I found this post helped me:

http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true

When doing this, make sure you are connected to the network if you are using a windows account.

Problem creating diagrams

I just upgraded an existing sql server 2000 installation with the RTM sql server 2005 (developer). I went to work on a database that I've been working on for a couple of weeks before the upgrade, and wanted to create a database diagram. However, when I click on the "Database Diagrams" tree node, I get this message:
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.

BUTTONS:
OK

The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.
Any ideas what's going on?
brian

I haven't been able to reproduce a case where setting the database owner didn't solve the problem.

I'm suspecting this has something to do with the upgrade process. When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode.

As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram? (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...) You can set the database back to SQL Server 2000 compatibility after this experiment.

Please let me know if this makes any difference.

|||Hi,

I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...

I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.

Any hints would be highly appreciated.

Thanks,
Florin|||I've filed a defect report for this issue.

Just to make sure I'm understanding you correctly:

1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
2) Your database is in SQL Server 2005 compatibility mode
3) Your database is read/write
4) Your database is owned by a valid login
5) You are logged in as an administrator
6) You are allowing Management Studio to try to install the diagram support objects
7) You don't get any error messages
8) You still can't create diagrams

I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected.

As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database?|||I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
1. Removed all schema's and logins associated with my login in each database
2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
3. Gave myself Sysadmin Role in samefolder under Roles
4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.

And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.

I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database to a User of dbo and default Schema of dbo.

Hope this helps. - Jeff|||Hi,

Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
I hope that helps in narrowing down the problem.

Thank you,
Florin|||

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

|||

Thank you very much, Steven,

For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.

Michelle.

|||

Thanks Steve,

I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..

thank u very much once again

Sree

|||

I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.

This fixed it;

1) Switch to compatibility mode 9.

2) Remove my old diagrams.

3) Switch back to compatibility mode 8.

I also ran this command.

ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]

|||

I'm having a similar problem with SQL Server Express in Visual Studio.

The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.

If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.

You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?

Thanks,

rich

|||

I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1

rich

|||

Hello,

I had the same problem without upgrading a database.

I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.

Even if you change then the Compatibility level back to SQL Server 2000 its ok.

Thanks.

Alex.

|||

Hi Michelle,

Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.

Thanks,

Ron

E-Mail: tdi358@.yahoo.com

|||

I found this post helped me:

http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true

When doing this, make sure you are connected to the network if you are using a windows account.

Problem creating diagrams

I just upgraded an existing sql server 2000 installation with the RTM sql server 2005 (developer). I went to work on a database that I've been working on for a couple of weeks before the upgrade, and wanted to create a database diagram. However, when I click on the "Database Diagrams" tree node, I get this message:
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.

BUTTONS:
OK

The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.
Any ideas what's going on?
brian

I haven't been able to reproduce a case where setting the database owner didn't solve the problem.

I'm suspecting this has something to do with the upgrade process. When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode.

As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram? (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...) You can set the database back to SQL Server 2000 compatibility after this experiment.

Please let me know if this makes any difference.

|||Hi,

I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...

I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.

Any hints would be highly appreciated.

Thanks,
Florin|||I've filed a defect report for this issue.

Just to make sure I'm understanding you correctly:

1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
2) Your database is in SQL Server 2005 compatibility mode
3) Your database is read/write
4) Your database is owned by a valid login
5) You are logged in as an administrator
6) You are allowing Management Studio to try to install the diagram support objects
7) You don't get any error messages
8) You still can't create diagrams

I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected.

As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database?|||I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
1. Removed all schema's and logins associated with my login in each database
2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
3. Gave myself Sysadmin Role in samefolder under Roles
4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.

And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.

I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database to a User of dbo and default Schema of dbo.

Hope this helps. - Jeff|||Hi,

Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
I hope that helps in narrowing down the problem.

Thank you,
Florin|||

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

|||

Thank you very much, Steven,

For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.

Michelle.

|||

Thanks Steve,

I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..

thank u very much once again

Sree

|||

I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.

This fixed it;

1) Switch to compatibility mode 9.

2) Remove my old diagrams.

3) Switch back to compatibility mode 8.

I also ran this command.

ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]

|||

I'm having a similar problem with SQL Server Express in Visual Studio.

The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.

If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.

You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?

Thanks,

rich

|||

I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1

rich

|||

Hello,

I had the same problem without upgrading a database.

I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.

Even if you change then the Compatibility level back to SQL Server 2000 its ok.

Thanks.

Alex.

|||

Hi Michelle,

Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.

Thanks,

Ron

E-Mail: tdi358@.yahoo.com

|||

I found this post helped me:

http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true

When doing this, make sure you are connected to the network if you are using a windows account.

Problem creating diagrams

I just upgraded an existing sql server 2000 installation with the RTM sql server 2005 (developer). I went to work on a database that I've been working on for a couple of weeks before the upgrade, and wanted to create a database diagram. However, when I click on the "Database Diagrams" tree node, I get this message:
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.

BUTTONS:
OK

The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.
Any ideas what's going on?
brian

I haven't been able to reproduce a case where setting the database owner didn't solve the problem.

I'm suspecting this has something to do with the upgrade process. When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode.

As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram? (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...) You can set the database back to SQL Server 2000 compatibility after this experiment.

Please let me know if this makes any difference.

|||Hi,

I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...

I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.

Any hints would be highly appreciated.

Thanks,
Florin
|||I've filed a defect report for this issue.

Just to make sure I'm understanding you correctly:

1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
2) Your database is in SQL Server 2005 compatibility mode
3) Your database is read/write
4) Your database is owned by a valid login
5) You are logged in as an administrator
6) You are allowing Management Studio to try to install the diagram support objects
7) You don't get any error messages
8) You still can't create diagrams

I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected.

As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database?|||I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
1. Removed all schema's and logins associated with my login in each database
2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
3. Gave myself Sysadmin Role in samefolder under Roles
4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.

And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.

I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database to a User of dbo and default Schema of dbo.

Hope this helps. - Jeff|||Hi,

Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
I hope that helps in narrowing down the problem.

Thank you,
Florin|||

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

|||

Thank you very much, Steven,

For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.

Michelle.

|||

Thanks Steve,

I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..

thank u very much once again

Sree

|||

I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.

This fixed it;

1) Switch to compatibility mode 9.

2) Remove my old diagrams.

3) Switch back to compatibility mode 8.

I also ran this command.

ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]

|||

I'm having a similar problem with SQL Server Express in Visual Studio.

The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.

If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.

You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?

Thanks,

rich

|||

I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1

rich

|||

Hello,

I had the same problem without upgrading a database.

I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.

Even if you change then the Compatibility level back to SQL Server 2000 its ok.

Thanks.

Alex.

|||

Hi Michelle,

Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.

Thanks,

Ron

E-Mail: tdi358@.yahoo.com

|||

I found this post helped me:

http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true

When doing this, make sure you are connected to the network if you are using a windows account.

Problem creating diagrams

I just upgraded an existing sql server 2000 installation with the RTM sql server 2005 (developer). I went to work on a database that I've been working on for a couple of weeks before the upgrade, and wanted to create a database diagram. However, when I click on the "Database Diagrams" tree node, I get this message:
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.

BUTTONS:
OK

The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.
Any ideas what's going on?
brian

I haven't been able to reproduce a case where setting the database owner didn't solve the problem.

I'm suspecting this has something to do with the upgrade process. When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode.

As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram? (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...) You can set the database back to SQL Server 2000 compatibility after this experiment.

Please let me know if this makes any difference.

|||Hi,

I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...

I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.

Any hints would be highly appreciated.

Thanks,
Florin
|||I've filed a defect report for this issue.

Just to make sure I'm understanding you correctly:

1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
2) Your database is in SQL Server 2005 compatibility mode
3) Your database is read/write
4) Your database is owned by a valid login
5) You are logged in as an administrator
6) You are allowing Management Studio to try to install the diagram support objects
7) You don't get any error messages
8) You still can't create diagrams

I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected.

As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database?|||I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
1. Removed all schema's and logins associated with my login in each database
2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
3. Gave myself Sysadmin Role in samefolder under Roles
4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.

And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.

I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database to a User of dbo and default Schema of dbo.

Hope this helps. - Jeff|||Hi,

Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
I hope that helps in narrowing down the problem.

Thank you,
Florin|||

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

|||

Thank you very much, Steven,

For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.

Michelle.

|||

Thanks Steve,

I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..

thank u very much once again

Sree

|||

I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.

This fixed it;

1) Switch to compatibility mode 9.

2) Remove my old diagrams.

3) Switch back to compatibility mode 8.

I also ran this command.

ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]

|||

I'm having a similar problem with SQL Server Express in Visual Studio.

The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.

If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.

You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?

Thanks,

rich

|||

I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1

rich

|||

Hello,

I had the same problem without upgrading a database.

I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.

Even if you change then the Compatibility level back to SQL Server 2000 its ok.

Thanks.

Alex.

|||

Hi Michelle,

Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.

Thanks,

Ron

E-Mail: tdi358@.yahoo.com

|||

I found this post helped me:

http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true

When doing this, make sure you are connected to the network if you are using a windows account.

Problem creating diagrams

I just upgraded an existing sql server 2000 installation with the RTM sql server 2005 (developer). I went to work on a database that I've been working on for a couple of weeks before the upgrade, and wanted to create a database diagram. However, when I click on the "Database Diagrams" tree node, I get this message:
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.

BUTTONS:
OK

The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.
Any ideas what's going on?
brian

I haven't been able to reproduce a case where setting the database owner didn't solve the problem.

I'm suspecting this has something to do with the upgrade process. When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode.

As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram? (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...) You can set the database back to SQL Server 2000 compatibility after this experiment.

Please let me know if this makes any difference.

|||Hi,

I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...

I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.

Any hints would be highly appreciated.

Thanks,
Florin|||I've filed a defect report for this issue.

Just to make sure I'm understanding you correctly:

1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
2) Your database is in SQL Server 2005 compatibility mode
3) Your database is read/write
4) Your database is owned by a valid login
5) You are logged in as an administrator
6) You are allowing Management Studio to try to install the diagram support objects
7) You don't get any error messages
8) You still can't create diagrams

I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected.

As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database?|||I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
1. Removed all schema's and logins associated with my login in each database
2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
3. Gave myself Sysadmin Role in samefolder under Roles
4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.

And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.

I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database to a User of dbo and default Schema of dbo.

Hope this helps. - Jeff|||Hi,

Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
I hope that helps in narrowing down the problem.

Thank you,
Florin|||

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

|||

Thank you very much, Steven,

For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.

Michelle.

|||

Thanks Steve,

I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..

thank u very much once again

Sree

|||

I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.

This fixed it;

1) Switch to compatibility mode 9.

2) Remove my old diagrams.

3) Switch back to compatibility mode 8.

I also ran this command.

ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]

|||

I'm having a similar problem with SQL Server Express in Visual Studio.

The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.

If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.

You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?

Thanks,

rich

|||

I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1

rich

|||

Hello,

I had the same problem without upgrading a database.

I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.

Even if you change then the Compatibility level back to SQL Server 2000 its ok.

Thanks.

Alex.

|||

Hi Michelle,

Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.

Thanks,

Ron

E-Mail: tdi358@.yahoo.com

|||

I found this post helped me:

http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true

When doing this, make sure you are connected to the network if you are using a windows account.

Problem copying table

We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)

FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.

So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)

Hopefully you have a test-environment where you can try the following procedure:

1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.

As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.

Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.