| Author |
Topic |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2002-10-23 : 00:45:15
|
| Hi my darlings...I've just been helping one of our admin guys here with a problem...SQL Server 7.0There is a user in the sysusers table which won't go away. When you try to add a login group that contains that user you get errors. When you try to add that user in explicitly, you get "user already exists" etc.The user does not show up in the Users folder in EM. (no matter how many times you refresh)All sessions are dead, and the server has just been restarted. (the network is disconnected too)Obviously I can't do delete sysusers where uid=120but gee I'd like to...Any ideas?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-10-23 : 01:13:40
|
| if you do sp_changeuserslogin 'report' does the user show up?If so, create a login for it and remap it using sp_changeuserslogin.What happens when you run sp_dropuser?-Chad |
 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-23 : 01:21:32
|
No luck I'm afraid Chad, quote:
if you do sp_changeuserslogin 'report' does the user show up?
noquote:
What happens when you run sp_dropuser?
It responds that the user does not exist in the current database (master or the DB I'm interested in)any more ideas?Edited by - rrb on 10/23/2002 23:10:46 |
|
|
|
Margaret
Starting Member
4 Posts |
Posted - 2002-10-25 : 09:03:01
|
| I have had issue's with the EM(GUI) not being up to date in the past. Did you try dropping the user via sp_droplogin or sp_dropuser. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-27 : 17:22:47
|
Thanks Margaret,you must have missed the bit where I saidquote:
No luck I'm afraid Chad, quote:-------------------------------------------------------------------------------- if you do sp_changeuserslogin 'report' does the user show up? --------------------------------------------------------------------------------no quote:-------------------------------------------------------------------------------- What happens when you run sp_dropuser? --------------------------------------------------------------------------------It responds that the user does not exist in the current database (master or the DB I'm interested in)
Can anyone else help?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-28 : 09:59:48
|
| I know this is not what you want to hear, but I would at this point backup my database and then recreate it and then DTS or bcp data over from the backed up database to the newly created one, and then you can recreate the users. This problem indicates to me some level of corruption, so if I were in this situation I cannot have a corrupt database, and therefore I would rebuild.***************************************Death must absolutely come to enemies of the code! |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-28 : 17:02:50
|
quote:
I know this is not what you want to hear,
Actually royv, I want to hear it. I'll try it and get back to you....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Lee
Starting Member
1 Post |
Posted - 2002-10-29 : 18:38:08
|
| I had the same problem once before. I thought I ran DBCC checkdb, and then it went away.Lee=================================I know this is not what you want to hear, [/quote]Actually royv, I want to hear it. I'll try it and get back to you....--I hope that when I die someone will say of me "That guy sure owed me a lot of money"[/quote] |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-29 : 18:44:27
|
| Hi LeeThanks - I'll give it a go.--I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 10/29/2002 18:45:29 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-29 : 19:01:51
|
| No luck - thanks for trying....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-30 : 09:39:48
|
| No luck with the DBCC or no luck with the rebuild?***************************************Death must absolutely come to enemies of the code! |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-30 : 19:08:30
|
| Apologies - I meant no luck with DBCCI haven't quite got to the rebuild yet....(looks like next on my list)--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
sherrer
64 Posts |
Posted - 2002-10-31 : 16:16:43
|
| This happens when id of the user associated with the database is different that the id in the server security. Normally caused by restoring a db on a different server than it was built with. Even if the user is added to the new server, the user will get a different id.You can set the property to allow modifications to system catalogs, then VERY CAREFULLY go to the sysusers table in your database and delete the user from the table. Once you have done so, you will be able to add the user back to the database with no problem. Then if I were you I would set the allow modifications back. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-10-31 : 16:21:32
|
| Did you run DBCC CHECKCATALOG?Also you might want to try running sp_revokedbaccess -- it is pretty much the same thing as sp_dropuser, except doesn't perform this "user doesn't exist in db check"...Deleting from sysusers might be tricky (you need to account for the related data in syspermissions and sysusers). You are better off with sp_revokedbaccessEdited by - izaltsman on 10/31/2002 16:26:19 |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-31 : 16:29:19
|
| Have you tried reciting poetry to it? I have had this problem before (soemthing simular atleast) and the recreation looks best. If you've identified this one, I would wonder if there are other cases of this (or other bugs) throughout your db that you haven't found yet. That was the reasoning used here. So a full recreate and the problem hasn't came back yet.It may be that it is deleted, only the GUI is still showing it. Can you still log in through this ID?-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-03 : 17:37:34
|
quote:
Have you tried reciting poetry to it?
Sorry, I only write lymmericks and I'm sure they don't qualify...sp_revokedbacess did the trick - thanks IlyaPS (Mark - you're totally right of course, a rebuild is in order. Will put it on his list of "things to do"  )Cheers all and thanks for the help - really appreciated!  --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
ksw
Starting Member
24 Posts |
Posted - 2002-11-12 : 11:29:18
|
| I just had the same issue. It must be a behavioral thing. I detached a database from one server, copied the files to a second server, and attached the db. The user didn't show up in EM, but it errored when I tried to create the user on the new server. Maybe if the user had already existed on the new server, the SQL Server could have automatically mapped the user correctly.I used izaltsman's suggestion of using sp_revokedbaccess and it worked perfectly.--KSW |
|
|
|
|
User won't go away