orphaned users in sys.sysusers

  • Hi everybody,

    we got 2 entries in this table

    [myDatabase].[sys].[sysusers]

    Is there a possibility to delete those entries?

    With DELETE ... we got this error:

    "Msg 259, Level 16, State 1, Line 4

    Ad hoc updates to system catalogs are not allowed."

    And further it is not possible with the reconfigure option.

    Thanks

    Kind regards,

    Andreas

     

  • Yeah, you can't update system catalogs.  We haven't been able to do that since SQL 2000, I think.  Also, sys.sysusers is an old view that's only there for  backward compatibility.  You should be using sys.database_principals.

    That said, you can't modify any of these views directly.  If you want to remove a user, use DROP USER <username>

    Greg

  • Hi,

    if I look at sys.database_principals, I am not able to see those users.

    And, if I am not able to see the user, I am not able to drop them.

    I think, I will do nothing, and I try to look away if I work in this database.

    Kind regards,

    Andreas

  • My mistake. Sysusers actually represents logins, not users.  I haven't referenced that view in a long time.  Logins get created at the server level and users get created at the database level.  You have logins that exist on the server, but have not been mapped to the database.  You can use DROP LOGIN if you're sure that you want to remove them.

    Greg

  • Hi,

    the users only exists in this view. I am not able to select them from sys.server_principals, or something like that.

    So, I think they are orphaned, and so I am not able to delete or to fix them.

     

  • They're not orphaned.  Orphaned users are in the database, but do not have a corresponding login associated with them.  You have the opposite.  These are server logins that have not had a database user created yet.  You can CREATE USER <username> FOR LOGIN <loginname> or you can drop the login if they are not needed.  I hope I'm making sense.

    Greg

  • Hm,

    but the only exists in the view in the database, so this should be users, and not logins, or? Logins should exists on the server level, not in the database.

    If I try to drop the user, or maybe the login, it will not be found, and I got an error.

     

     

  • what was the original version of this database, im guessing it came from a very early version.

    You're saying you dropped all users from the database but sysusers still shows 2 users?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi,

    yes of course, the entries in the table are from 2003. I think, the database was migrated many time to the newest version.

    We did not dropped all users, because the application is still running. We saw the 2 entries, and we thought, okay, lets drop or remove them.

     

     

  • and theyre definitely sql user not db or app role or some other entity?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 10 posts - 1 through 9 (of 9 total)

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