Cant get diagrams to work in SQL Management Studio

I can't get diagrams to work in SQL Management Studio. When I try to open that folder in the object browser, I get:
"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."
That's great, but what exactly constitutes a "valid login" and who or what is it supposed to be?
If you follow the first set of steps, click the button with theellipsis next to the owner textbox, and you'll get a list of validlogins that you can choose an owner from. Then save it, andyou should be ready to go.
|||But other than myself (the one it's already set to), there are no otheraccounts other than system accounts. How is me being an owner not goodenough?
|||Huh. Well, if you go to the Security node for your SQL Serverinstance, and look at the Logins, is your account in there? If itis, and you're still getting the error, you might try changing the DBowner to another account, save it, and then change it back toyourself.
|||Tried all of the above. No joy. I am the owner, or so it says.
This is seriously annoying, and in true MS documentation fashion, the docs are not helpful.
|||Was this database by any chance restored from another machine? Maybe a machine on another domain? Or have there been changes tothe domain since the database was created? That's the last thingthat I can think of...That even though the account *looks* to be thesame on the surface, that underneath, it's got a different SID than theaccount that's been granted access to the SQL Server instance. Ifthat's the case, then the remedy would be to delete your accountentirely from list of the database's users, and then re-add it. If that's not the solution, hopefully someone else will have a tip,'cause I'm out of ideas.
|||The database was upgraded from the old 2000 Dev Edition to the 2005 DevEdition. I ended up scripting the database, nuking it, and recreatingit since there was nothing critical about the actual data. All is wellnow. It was still annoying. :)
|||

Solution, you need to go:

Properties of your database
Options
Compatibility level and here select SQL Server 2005
Click OK
Go to Database Diagrams and it will ask you if you want to create it , put yes and you will get your diagrams.
Hope it helps.
Sorry for my english im from Argentine.

|||I think it had to do with the compatibility level. I had the same with a restored 2000 database on 2005. Couldn't access the diagrams. But after setting the compatibility level (properties of database then the options page) to SQL Server 2005 (90), I was able to access them. (some didn't work though)
gr Rc

Can't find objects

I have a database with a bunch of objects owned by a user named, "tims". I a
m
logged into SQL Query Analyzer as the tims user, but I can't locate objects
unless I use I qualify them with the owner name. For example,
exec sp_select_corridors #doesn't work
exec tims.sp_selelct_corridors #works
BTW, this was working before. I recently did a DTS import on another
database on this server. I am wondering if the tims login got messed up. If
so, how would I fix it?
Thanks!
- JohnnyI would drop the objects and re-create them, specifying dbo. as the owner...
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:8E5E39D7-E65D-44CB-81CE-DD5324A475FA@.microsoft.com...
>I have a database with a bunch of objects owned by a user named, "tims". I
>am
> logged into SQL Query Analyzer as the tims user, but I can't locate
> objects
> unless I use I qualify them with the owner name. For example,
> exec sp_select_corridors #doesn't work
> exec tims.sp_selelct_corridors #works
> BTW, this was working before. I recently did a DTS import on another
> database on this server. I am wondering if the tims login got messed up.
> If
> so, how would I fix it?
> Thanks!
> - Johnny|||Can you check if there is another sp with the same name?

> exec sp_select_corridors #doesn't work
Any error?
AMB
"JohnnyMagz" wrote:

> I have a database with a bunch of objects owned by a user named, "tims". I
am
> logged into SQL Query Analyzer as the tims user, but I can't locate object
s
> unless I use I qualify them with the owner name. For example,
> exec sp_select_corridors #doesn't work
> exec tims.sp_selelct_corridors #works
> BTW, this was working before. I recently did a DTS import on another
> database on this server. I am wondering if the tims login got messed up. I
f
> so, how would I fix it?
> Thanks!
> - Johnny

can't drop user, owns objects

I migrated copies of a database to a test server, but I cannot log on. My
admin account needs to be dropped and readded but every time I run
sp_dropuser in query analyzer I get the error :
Msg 15183, level 16, state 1, ...
"the user owns objects in the database and cannot be dropped."
then a list of the objects (EG TMP_SYSA_1234) is displayed.
How can I delete these objects? I assume then I will be able to delete the
user and recreate him.
On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> I migrated copies of a database to a test server, but I cannot log on. My
> admin account needs to be dropped and readded but every time I run
> sp_dropuser in query analyzer I get the error :
> Msg 15183, level 16, state 1, ...
> "the user owns objects in the database and cannot be dropped."
> then a list of the objects (EG TMP_SYSA_1234) is displayed.
> How can I delete these objects? I assume then I will be able to delete the
> user and recreate him.
Hi,
You're perfectly right... This error means that some objects are still
owned by this user.
In order to drop each object, you will first need to know what kind of
objects are owned by the user you would like to delete. Then, you'll
be able to run the correct drop commands in the query analyser...
As an example, here are some usual drop commands :
DROP TABLE [schema].[Table name]
DROP VIEW [schema].[View name]
DROP PROCEDURE [schema].[Procedure name]
...
Whenever all the objects owned will be dropped, you'll be able to use
the sp_dropuser procedure.
Hope this helps,
Cdric Del Nibbio
MCTS SQL Server 2005
MCSD .NET
MCT
http://cedric-delnibbio-sql.blogspot.com
|||thank you, Cedric
"Cédric Del Nibbio" wrote:

> On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> Hi,
> You're perfectly right... This error means that some objects are still
> owned by this user.
> In order to drop each object, you will first need to know what kind of
> objects are owned by the user you would like to delete. Then, you'll
> be able to run the correct drop commands in the query analyser...
> As an example, here are some usual drop commands :
> DROP TABLE [schema].[Table name]
> DROP VIEW [schema].[View name]
> DROP PROCEDURE [schema].[Procedure name]
> ...
> Whenever all the objects owned will be dropped, you'll be able to use
> the sp_dropuser procedure.
> Hope this helps,
> Cédric Del Nibbio
> MCTS SQL Server 2005
> MCSD .NET
> MCT
> http://cedric-delnibbio-sql.blogspot.com
>

can't drop user, owns objects

I migrated copies of a database to a test server, but I cannot log on. My
admin account needs to be dropped and readded but every time I run
sp_dropuser in query analyzer I get the error :
Msg 15183, level 16, state 1, ...
"the user owns objects in the database and cannot be dropped."
then a list of the objects (EG TMP_SYSA_1234) is displayed.
How can I delete these objects? I assume then I will be able to delete the
user and recreate him.On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> I migrated copies of a database to a test server, but I cannot log on. My
> admin account needs to be dropped and readded but every time I run
> sp_dropuser in query analyzer I get the error :
> Msg 15183, level 16, state 1, ...
> "the user owns objects in the database and cannot be dropped."
> then a list of the objects (EG TMP_SYSA_1234) is displayed.
> How can I delete these objects? I assume then I will be able to delete the
> user and recreate him.
Hi,
You're perfectly right... This error means that some objects are still
owned by this user.
In order to drop each object, you will first need to know what kind of
objects are owned by the user you would like to delete. Then, you'll
be able to run the correct drop commands in the query analyser...
As an example, here are some usual drop commands :
DROP TABLE [schema].[Table name]
DROP VIEW [schema].[View name]
DROP PROCEDURE [schema].[Procedure name]
.=2E.
Whenever all the objects owned will be dropped, you'll be able to use
the sp_dropuser procedure.
Hope this helps,
C=E9dric Del Nibbio
MCTS SQL Server 2005
MCSD .NET
MCT
http://cedric-delnibbio-sql.blogspot.com|||thank you, Cedric
"Cédric Del Nibbio" wrote:

> On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> Hi,
> You're perfectly right... This error means that some objects are still
> owned by this user.
> In order to drop each object, you will first need to know what kind of
> objects are owned by the user you would like to delete. Then, you'll
> be able to run the correct drop commands in the query analyser...
> As an example, here are some usual drop commands :
> DROP TABLE [schema].[Table name]
> DROP VIEW [schema].[View name]
> DROP PROCEDURE [schema].[Procedure name]
> ...
> Whenever all the objects owned will be dropped, you'll be able to use
> the sp_dropuser procedure.
> Hope this helps,
> Cédric Del Nibbio
> MCTS SQL Server 2005
> MCSD .NET
> MCT
> http://cedric-delnibbio-sql.blogspot.com
>sql

Can't delete SQL user because they own objects

How do I find out what objects they own and then how do I drop those objects
so I can delete the user?
Goto Security >>
Logins >>
Properties of the User you want to delete >>
"Database Access" Tab >>
under the "User" column , you will find all of the databases he owns
because "dbo" will be showing, then you can delete the db's if you want
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> How do I find out what objects they own and then how do I drop those
objects
> so I can delete the user?
>
|||Hi Dan
You could run the following:
select table_name, table_type from information_schema.tables
where table_schema = 'user_name'
This will return both tables and views. You can use DROP TABLE for the
tables and DROP VIEW for the views, and you must preface the object name
with the user_name of the owner in the drop command.
You could also just change the owner of these objects with
sp_ChangeObjectOwner if you don't want to drop them.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> How do I find out what objects they own and then how do I drop those
objects
> so I can delete the user?
>
|||So I have to do it table by table or is there someway to find out what
objects the user owns? There are close to a hundred tables in the database
that I want to delete the user from. Seems like there should be a better
way and yes I don't want to delete any database objects (I guess I worded
that wrong) just change ownership or whatever it takes to delete the user.
Thanks,
Dan
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23$NADUcGEHA.704@.tk2msftngp13.phx.gbl...
> Hi Dan
> You could run the following:
> select table_name, table_type from information_schema.tables
> where table_schema = 'user_name'
> This will return both tables and views. You can use DROP TABLE for the
> tables and DROP VIEW for the views, and you must preface the object name
> with the user_name of the owner in the drop command.
> You could also just change the owner of these objects with
> sp_ChangeObjectOwner if you don't want to drop them.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Dan Marth" <danmarth@.earthlink.net> wrote in message
> news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> objects
>
|||Two possibilities, both of which use undocumented features:
1. Direct update of system tables. If you've never done this before, this is
not a good time to start.
update sysobjects
set uid = <user_id of some other user>
where uid = <uid of user you want to delete.
2. Use the undocumented procedure sp_execresultset to build a select
statement that generates the sp_changeobjectowner commands, and then
executes them. This is a little tricky, so you should practice in a test db
first.
First construct the select that builds the commands:
select 'exec sp_changeobjectowner [olduser.' + table_name + '], newuser'
from information_schema.tables
where table_schema = 'olduser'
Substitute usernames as appropriate. When you run the above you should get a
whole bunch of EXEC sp_changeobjectowner commands in the result window. You
can either copy from the result window, and paste into the query window, and
execute, OR
wrap the whole thing in sp_execresultset.
All the quotes in the original command must be turned into two single quotes
in the new command, because the whole thing (the argument to
sp_execresultset) is wrapped in single quotes:
exec sp_execresultset 'select ''exec sp_changeobjectowner [olduser.'' +
table_name + ''], newuser'' from information_schema.tables
where table_schema = ''olduser'' '
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:#Fg$EqeGEHA.1396@.TK2MSFTNGP11.phx.gbl...
> So I have to do it table by table or is there someway to find out what
> objects the user owns? There are close to a hundred tables in the
database
> that I want to delete the user from. Seems like there should be a better
> way and yes I don't want to delete any database objects (I guess I worded
> that wrong) just change ownership or whatever it takes to delete the user.
> Thanks,
> Dan
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23$NADUcGEHA.704@.tk2msftngp13.phx.gbl...
>

Can't delete SQL user because they own objects

How do I find out what objects they own and then how do I drop those objects
so I can delete the user?Goto Security >>
Logins >>
Properties of the User you want to delete >>
"Database Access" Tab >>
under the "User" column , you will find all of the databases he owns
because "dbo" will be showing, then you can delete the db's if you want
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> How do I find out what objects they own and then how do I drop those
objects
> so I can delete the user?
>|||Hi Dan
You could run the following:
select table_name, table_type from information_schema.tables
where table_schema = 'user_name'
This will return both tables and views. You can use DROP TABLE for the
tables and DROP VIEW for the views, and you must preface the object name
with the user_name of the owner in the drop command.
You could also just change the owner of these objects with
sp_ChangeObjectOwner if you don't want to drop them.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> How do I find out what objects they own and then how do I drop those
objects
> so I can delete the user?
>|||So I have to do it table by table or is there someway to find out what
objects the user owns? There are close to a hundred tables in the database
that I want to delete the user from. Seems like there should be a better
way and yes I don't want to delete any database objects (I guess I worded
that wrong) just change ownership or whatever it takes to delete the user.
Thanks,
Dan
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23$NADUcGEHA.704@.tk2msftngp13.phx.gbl...
> Hi Dan
> You could run the following:
> select table_name, table_type from information_schema.tables
> where table_schema = 'user_name'
> This will return both tables and views. You can use DROP TABLE for the
> tables and DROP VIEW for the views, and you must preface the object name
> with the user_name of the owner in the drop command.
> You could also just change the owner of these objects with
> sp_ChangeObjectOwner if you don't want to drop them.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Dan Marth" <danmarth@.earthlink.net> wrote in message
> news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> objects
>|||Two possibilities, both of which use undocumented features:
1. Direct update of system tables. If you've never done this before, this is
not a good time to start.
update sysobjects
set uid = <user_id of some other user>
where uid = <uid of user you want to delete.
2. Use the undocumented procedure sp_execresultset to build a select
statement that generates the sp_changeobjectowner commands, and then
executes them. This is a little tricky, so you should practice in a test db
first.
First construct the select that builds the commands:
select 'exec sp_changeobjectowner [olduser.' + table_name + '], newuser
'
from information_schema.tables
where table_schema = 'olduser'
Substitute usernames as appropriate. When you run the above you should get a
whole bunch of EXEC sp_changeobjectowner commands in the result window. You
can either copy from the result window, and paste into the query window, and
execute, OR
wrap the whole thing in sp_execresultset.
All the quotes in the original command must be turned into two single quotes
in the new command, because the whole thing (the argument to
sp_execresultset) is wrapped in single quotes:
exec sp_execresultset 'select ''exec sp_changeobjectowner [olduser.'' +
table_name + ''], newuser'' from information_schema.tables
where table_schema = ''olduser'' '
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:#Fg$EqeGEHA.1396@.TK2MSFTNGP11.phx.gbl...
> So I have to do it table by table or is there someway to find out what
> objects the user owns? There are close to a hundred tables in the
database
> that I want to delete the user from. Seems like there should be a better
> way and yes I don't want to delete any database objects (I guess I worded
> that wrong) just change ownership or whatever it takes to delete the user.
> Thanks,
> Dan
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23$NADUcGEHA.704@.tk2msftngp13.phx.gbl...
>

Can't Create Database Diagram Support Objects

I recently moved a database from SQL Server 2000 to 2005. Now when I try to
create a diagram I get the following error:
========================= Database diagram support objects cannot be installed because this database
does not have a valid owner. To continure, 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.
=========================
The database owner is 'sa'. I thought it might be a problem with an orphaned
user, but EXEC sp_change_users_login 'Update_One', 'sa', 'sa'; doesn't work
for 'sa'.
What can I do to resolve this problem?On Aug 29, 12:54 pm, Dan <dantheri...@.newsgroup.nospam> wrote:
> I recently moved a database from SQL Server 2000 to 2005. Now when I try to
> create a diagram I get the following error:
> =========================> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continure, 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.
> =========================> The database owner is 'sa'. I thought it might be a problem with an orphaned
> user, but EXEC sp_change_users_login 'Update_One', 'sa', 'sa'; doesn't work
> for 'sa'.
> What can I do to resolve this problem?
I seem to remember having the same issue and I could be wrong but:
Right click on the database, Properties, Options and set the
Compatibility Mode to SQL Server 2005 (90) and see if that works for
you.
There are implications of doing this, such as old SQL server syntax
will no longer be supported, so you will want to fully examine the
compatibilty of your database to SQL 2005 before doing this in your
production environment.|||Hi Dan,
This is a known issue. You can resolve it via:
Change the database compatibility level to 2005, install database diagram
support, and then return the database to the desired database compatibility
level.
For more information, please refer to the section 4.8.1 in this KB article:
SQL Server 2005 Readme and installation requirements
http://support.microsoft.com/default.aspx?scid=kb;EN-US;910228
Hope this helps. Please feel free to let us know if you have any other
questions or concerns. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================