May 11, 2007 at 5:56 pm
Hi everybody,
I used Enterprise Manager and deleted some logins and removed those users from the databases. Everything looks good there. But when I use Query Analyzer and execute sp_helpuser in the databases, the users that I thought were removed through EM are still there in the databases. I checked the sysuser table in the databases and those users still exist in the table. I used EM on another computer and checked -- the users are still in the databases although the logins are removed. But EM on my machine shows they are gone.
I refreshed, closed EM and reopened EM. Still the same -- the users were not removed from the server but EM on my machine showing users gone. So I uninstalled and reinstalled EM (SQL Client Tools) using SQL Server Setup CD -- still the same. (BTW, I only had/have SQL 2000 Client tools on my machine). Did some research and found 'How to manually remove SQL Server 2000 default, named or virtue instance' on microsoft website. So after uninstall again, did what the article says -- removed the empty SQL folders in Program Files and deleted SQL Server registry keys from my machine. Reinstalled SQL Client tools. Open EM -- still the same.
I don't know what else I could do to have EM telling the truth other than reimage my machine and reinstall OS. Has anybody seen this and any advices/suggestions?
Thanks in advance!
May 13, 2007 at 10:49 pm
Logins and users are not the same thing. Logins are server level and users are database levels. A login is what gives soemone the ability to access the server. The user account is used to manage the a user's permissions on a per database level.
May 13, 2007 at 11:42 pm
Thanks for your reply.
I know logins and users are different -- logins let someone to get to the server but by adding the logins as users in databases and granting them rights the users have rights in the databases. My problem is that when I removed some users from some databases and deleted those same logins from the server using EM, my EM shows that they are gone from the databases and gone from the server. But by using my QA or EM on another machine, the logins are gone but the users are still in the databases - they are left as orphaned users. Something is wrong with my EM that even uninstall and reinstall wouldn't fix. It's wierd and maybe people has never seen this kind of thing.
May 14, 2007 at 9:08 am
Re-installing client tools on your workstation/laptop would not help in your case as the data is stored on the server. EM is not really corrupted, it just shows incorrect info when the data in underlying system tables are mixed up, which is clearly your case.
Using QA and assuming you have at least db_owner access, try to remove users from each database using sp_revokedbaccess or sp_dropuser . This should solve the problem.
HTH
May 14, 2007 at 11:34 am
Thank you for your reply. It makes sense. It's strange that my EM showed different info from EM on other machines and QA results. EM as a user interface uses SQL DMO to do work on the server...
I used QA and removed all those orphaned users in the databases. All look good now. Hope my EM is not corrupted because I use both EM and QA. Sometimes SQL DMO.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply