Post reply

how to delete orphaned users in sysusers

  • January 11, 2005 at 4:44 pm

    #87266

    I have a couple users that appear in the sysusers table, that i cannot get rid of.  they dont seem to exist anywhere else.  I cannot do a drop login or drop user, sql doesnt think they exist... but there they are in the sysusers table.  I do not want to make it editable, and delete it that way.  I want clean way to remove them, though. 

    They show up in my reports of user access that i have to produce for sarbanes oxley.

    I have to justify their existence, but cannot. 

     

    I imagine they appeared at one time, were legitimate users, but after multiple copies of the database had been restored to multiple servers... there they are.

     

    thanks

  • January 11, 2005 at 5:14 pm

    #536921

    This stored procedure will remove them out of your database.

    EXEC sp_revokedbaccess 'xxusername'

     

    mom

  • July 19, 2011 at 8:32 am

    #1354852

    In SQL 2008 R2 the EXEC sp_revokedbaccess 'domain\UserName' does not remove the user from the sysusers table.

    I have tried sp_revokedbaccess,sp_dropuser, and sp_droplogin with and without [], '' and with the domain and without it. The few users in the sysusers table are no longer at the company and they do not have domain accounts.

    Is there any way to get the strays (orphaned users) out of the sysusers table?

    The users do not show up in any of these queries, but they still are in sysusers.

    SELECT A.name , A.principal_id , A.type_desc

    FROM sys.database_principals A , sys.server_principals B

    WHERE A.sid = B.sid

    SELECT * FROM sys.syslogins

    SELECT * FROM sys.database_principals

    sp_helpuser does not show the user either.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply