September 21, 2005 at 11:35 am
Normally when there is a problem deleting a user or login it is due to a SID mismatch between the Master.SysLogins and MyDatabase.SysUsers tables.
I have encountered a case where the SIDs match, but SQL Server insists the user does not exist in each database, but can't delete the login because it is "aliased or mapped to a user in one or more databases".
I can hack the system tables to fix this, but I am wondering if there is a more graceful way to fix this.
Following is what I have tried, and the results, while attempting to delete the Standard login "guest". There are 4 databases involved, but the one I am running against here is "Reporting" or Master.
sp_droplogin 'guest'
Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
Login 'guest' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.
sp_dropuser 'guest'
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'guest' does not exist in the current database.
sp_dropalias 'guest'
Server: Msg 15134, Level 16, State 1, Procedure sp_dropalias, Line 51
No alias exists for the specified user.
sp_revokedbaccess 'guest'
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'guest' does not exist in the current database.
Select * from Reporting..sysusers
Where name = 'guest'
2 0 guest 0x4773A75B321B654EA76678C2C203359E 0x00 1998-11-13 02:58:39.280 2005-09-21 10:58:51.517 0 NULL 0 NULL 0 1 0 0 0 1 0 0 0
Select M.name, D.name, M.sid, D.SID
From sysUsers D Inner Join Master.dbo.syslogins M On D.sid = M.sid
Where m.name = 'guest'
And M.SID = D.SID
guest guest 0x4773A75B321B654EA76678C2C203359E 0x4773A75B321B654EA76678C2C203359E
As you can see the SIDs match, but SQL just cannot figure out that it should delete the user.
Has anyone run into this before?
Thanks,
Chris
September 21, 2005 at 11:44 am
You have what is known as an 'orphaned user'. Look that up in the BOL. In the Index tab, enter 'orphaned user', double click on the entry, then choose troubleshooting orphaned users.
-SQLBill
September 21, 2005 at 11:58 am
This is not an orphaned user problem, as the SIDS match. I just found some more info and typed about 2 pages worth of information about what I found, but for some reason this site deleted that information and refreshed the post page when I hit the "Post" button, so I lost it all, and don't feel like retyping it again.
Short version, I believe "guest" is a legacy user, and sp_RevokeDBAccess has code in it that says not to drop a user named "guest".
Thanks,
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply