Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.sql

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

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 creating a job step to execute a SSIS package

Hello all,

I am having a problem creating a job step that will run a SSIS package. I go to add a job step (or edit an existing one), but when I select 'SQL Server Integration Services Package' as the type, an error box pops up with the following message:

The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)

After I hit OK, it goes back to the 'New Job Step' window and makes me select a different step type.

I am running on a Win2000 Advanced Server with SP4, a default instance of SQL2000 with SP4, and a named instance of SQL2005.

Does anyone know what is wrong or of a workaround?

Thanks in advance for any and all help/comments.

Jarret

You should probably post to the agent forum since this is an agent problem not an SSIS problem.

Thanks,
Matt

Problem creating a job step to execute a SSIS package

Hello all,

I am having a problem creating a job step that will run a SSIS package. I go to add a job step (or edit an existing one), but when I select 'SQL Server Integration Services Package' as the type, an error box pops up with the following message:

The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)

After I hit OK, it goes back to the 'New Job Step' window and makes me select a different step type.

I am running on a Win2000 Advanced Server with SP4, a default instance of SQL2000 with SP4, and a named instance of SQL2005.

Does anyone know what is wrong or of a workaround?

Thanks in advance for any and all help/comments.

Jarret

Whatever the problem was, restarting the server fixed it. I hadn't restarted the server since I installed SQL2005, because the install didn't require a reboot. I had a chance to do so over the weekend and when I checked on this problem, it was gone.

All is good now.

Thanks!

Jarret