| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-10-02 : 22:03:53
|
| A buddy of mine has restored an SQL 7 database. The Login "joe" cannot be deleted from the database because "it owns objects"... only I can't find the objects it owns.I'd like to delete Login "joe" from the database so we can assign "joe" on the server to that database.Sam |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-10-02 : 22:34:34
|
| You can change the owner of the objects to dbo instead of joe, and you can delete login joe from the DB. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-02 : 23:04:22
|
quote:
Originally posted by Westley
You can change the owner of the objects to dbo instead of joe, and you can delete login joe from the DB.
If I could find an object owned by Joe I would !  |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-03 : 00:01:50
|
| Have your ran the sp_change_users_login or whatever it's called on the database?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-03 : 00:39:30
|
| maybe an sp_changedbowner 'sa' also |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-03 : 01:06:09
|
| sp_change_users_login is new to SQL 2000. (This is SQL 7).All the objects I can see using EM are owned by dbo. Isn't there a query I can run against sysobjects that'll show me what object(s) Login "Joe" is thought to own?Sam |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-10-03 : 01:32:52
|
| maybe its not tables? maybe its a store proc or views? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-03 : 01:35:39
|
| Match the uid in sysobjects to the uid in sysusers?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-03 : 08:49:03
|
quote:
Originally posted by derrickleggett
Match the uid in sysobjects to the uid in sysusers?
I never *ever* look in these tables, so this is what I needed.select top 10 * from sysobjects so inner join sysusers su on su.uid = so.uid where su.name = 'joe' -- I'm learning things I never wanted to know...  |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-03 : 08:55:00
|
| Oops... How do I add a WHERE to specify the DB name I'm concerned about? Looks like sysdatabases holds the db names, the key is dbid, but I don't see a similar key in sysobjects.Someone's going to tell me to read a book. I just know it. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-03 : 09:44:36
|
You have to run that in each database. Think about it.  Long day???MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-03 : 13:22:55
|
quote:
Originally posted by derrickleggett
You have to run that in each database. Think about it. Long day???
I thought these sysxxxx tables were held in Master. Would you look at that! They're in every database...  It's hard enough understanding my own data. Now I gotta learn Microsoft's. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-03 : 13:37:28
|
"They're in every database..."Well ... sysdatabases isn't  Kristen |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-04 : 10:02:42
|
quote:
Originally posted by Kristen
Well ... sysdatabases isn't
Hey ! Stop having fun at my expense ! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-04 : 14:09:50
|
| I suppose you could put a VIEW in each DB to master.dbo.sysdatabases, and call it ... ermmm ... "sysdatabases" "Stop having fun at my expense"Sorry, only just read that bit :)Kristen |
|
|
|
|
Login owns objects... NOT !