db "dissappears" after sysUsers table Modification

  • I'm afraid I effectively ruined a database with some ignorance about modifying system tables.  I probably need to start over but thought I'd ask here in case I can learn from this.

    I had an account that somehow had "DBO" as its username.  This was causing problems removing or editing the permissions of this account. I am a wusiwug SQL administrator for the most part and was unable to get around this issue.  So I enabled directly editing the system tables and opened up the sysUsers table for this DB.  I changed the username from DBO to DBO2.  This change apparently broke the whole database.  Now when I try to access the DB Tables, I get "Invalid Object name 'dbo.sysobjects' and no tables are displayed.  What did I do?

    Since it was so easy to create this situation, I'm hoping there might be a way out? 

    Thanks,

    Stephen

  • I think the problem started when you got the "SIDS" out of sync between the master (syslogins) and database tables.  The user "sa" is mapped to dbo in each individual database. So, if sa has id# 123 in syslogins, and dbo has id# 456 in sysusers, this will cause the symptoms you describe.

    Was this database restored from a different server? This usually causes problems such as this.

    Try this, hope it helps.

    Log on to the database server as a domain administrator.  Start Enterprise Manager, and make sure that the registration properties are set to "Windows Authentication", if they ae not, change the registration properties.

    Get Enterprise manager started, and under "secuity", "logins" find the user sa.  Double click sa and try to give them access to the bad database.

    If it works, you are good. 

    But, you most likely will get errors.  If the error is "user already exists in database", then the sids are out of sync.

    Query the syslogins table in master.

    SELECT sid, name

    FROM syslogins

    where name = 'sa'

    You should get the value of SID = 0X1 for sa.

    Create another user in Enterprise Manager, using SQL authentication, and give it system administrator rights.

    Start Query Analyzer, logon as this new admin user.

    Connect to the bad database, and attempt to update the sysusers table.

    UPDATE sysusers SET SID = 0X1 WHERE name = 'dbo' OR name = 'dbo2'

    OR

    UPDATE sysusers SET name = 'dbo'

    OR name = 'dbo2'

     

     

     

     

     

     

     

     

     

     

     

     

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Thank you for your reply. 

    Before I saw your post, I was able to use query analyzer to place the name 'dbo' back into the name field, replacing 'dbo2'.  After a database restart this seemed to get things back to normal.

    But it does seem that I have a corrupted user/login system.  I know this particular database is fairly new and was created by Sharepoint Services.  It is the configuration database.  I don't remember, but it is possible I restored the whole SQL Server Database from my development box years back. 

    I checked  the sid of SA in the syslogins table and it was 0X01.  The username DBO in other databases is associated with an SID of 0x0105000000051...  So, It sounds like I should replace the dbo sid value in all of my databases to match the SA account SID.  Is this correct?

    Thanks again

  • If you can, rather than modifying the system tables, try changing the owner of the dbs in question to some other user, your own Login for example, with sp_changedbowner.  Then change it to 'sa'.  Should take care of any issues. 

    Steve

  • The  system proc sp_change_users_login may be able to help you fix the corrupted logins.

    exec sp_change_users_login @Action 'Report'

    Will list users in the current database that are not correctly linked to a login on SQL Server. 

    sp_change_users_login @Action = 'Auto_Fix' @UserNamePattern = 'username'

    Will link up the database user with the same login name on sql server.

    Hint: Disable updating of system tables to prevent yourself (or someone else) from doing this again. sp_configure @configname = 'allow updates', @configvalue = 0

     


    Julian Kuiters
    juliankuiters.id.au

  • Thanks for all the help!

Viewing 6 posts - 1 through 5 (of 5 total)

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