Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    2

    Exclamation Unanswered: Login/User problems after restore



    I have a question regarding SQLSERVER. I have 2 systems, production and development. I have restored a copy of the production database into the develeopment server to do some testing but am now having problems connecting to the database.

    The issue
    ======

    The database tables in the restored database are owned by a user HEATPROD in production and are stiil owned by that user after the restore. The USERS tab in SQL Server Enterprise Manager for this database does not show the user HEATPROD although the TABLES tab shows that user as owning the tables.

    I have created the LOGIN of HEATPROD but am not able to grant this login access to the restored database as I get the error "ERROR 15023: USER OR ROLE HEATPROD ALREADY EXISTS IN THE CURRENT DATABASE".

    An attemp to login as the HEATPROD user and access the new database generates the following error "SERVER USER HEATPROD IS NOT A VALID USER IN DATABASE DBATESTDB" however attempts to add this user to the database generate the 15023 error above.

    Any suggestion on how to link the LOGIN to the USER and thus gain access to the database would be much appreciated.

    TIA

  2. #2
    Join Date
    Feb 2002
    Posts
    28
    It sounds as if you have an "orphaned user" in your database called HEATPROD.

    To check this run the following

    Use <Insert DB Name>
    go
    Select suid, name from sysusers
    where name = 'HEATPROD'


    If it does exist you need to delete it from this table.
    To do that run the following .....


    Use <Insert DB Name>
    go
    sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    go
    delete from sysusers where name = 'HEATPROD'
    go
    sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    go


    When you have deleted the user, Refresh your database through Enterprise Manager.

    You can now add the user to your database ..... providing a login exists of course.

    Hope this helps

    P.C. Vaidyanathan

  3. #3
    Join Date
    Apr 2002
    Posts
    7
    The problem is that in the master database there is no user with that name.
    This can happen when you restore a dabase to another server or domain. Look at microsoft for the solution Q218172

  4. #4
    Join Date
    Feb 2002
    Posts
    7
    Running

    EXEC sp_change_users_login 'Auto_Fix', 'HEATPROD'

    Should do the trick if you are running sql server authentication


    Rosko

  5. #5
    Join Date
    May 2002
    Posts
    2
    Thanks,


    It worked as suggested.

  6. #6
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    3
    This is a script I use to correct logins after restoring from one server to another. The results can be copied to the active pane (where you can run the script) to fix all orphaned users for the database in question.


    select 'sp_change_users_login @Action = ' + char(39) + 'auto_fix' +
    char(39) + ', @usernamepattern = ' + char(39) + name + char(39) + char(13) + 'go'
    from sysusers
    where issqluser = 1 and (sid is not null and sid <> 0x0)
    and suser_sname(sid) is null
    order by name


    Hope this helps.

    Rory

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •