none
MS SQL Error 15023

    Question

  • I have three different environments in my database: DEV=Development, TRAIN=Training and PROD=Production, When I copy PROD onto TRAIN or DEV I do it by creating a Backup of PROD then restoring it onto TRAIN and DEV. Then I change the Login Properties User mapping. this works fine for the TRAIN environment, but for DEV when I try to check the 'master' dbo as public, I get back Error 15023. I tried droping the owner and setting again, but no luck. any suggestions?
    Tuesday, September 22, 2009 12:36 PM

Answers

  • You are receiving 15023 error as you are restoring a MS SQL database from a backup. Keep in mind that when you perform backup and restore across the sql servers you are only restoring a user database and not the master database where logins are kept.

    You expect a restored database to be in exactly the same state as the backup, but the login fails for a user that had permissions in the backed up database.   This is caused by Security identification numbers (SID) that are mismatched or 'orphaned' in the sysusers table. 


    a)

    Use [YourDatabase]
    Go

    this will report all the orphaned login
    EXEC sp_change_users_login @action='Report';

    this should fix a orphaned user
    Exec sp_change_users_login @action='Update_One', @UserNamePattern= [orphaneduserDBUserName] , @LoginName=[orphaneduserloginname]

    b) or you can search for a script that can do this for all the orphaned users

        http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

           http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30834/ (I think this site requires you to register)

     

    Tuesday, September 22, 2009 1:41 PM
  • Hi,

    Explain the below mention points to understand the issue.

    PROD-
    MSSQL Server version
    MSSQL Server SP

    Dev-
    MSSQL Server version
    MSSQL Server SP

    Login Property 

    Windows Login
    SQL login.


    Microsoft reported as BUG for sql 2000 behaviour, Try it..
    http://support.microsoft.com/kb/819261
    User logons and permissions on a database may be incorrect after the database is restored
    http://support.microsoft.com/kb/168001

    BR
    Praveen Barath

    -Mark as ANSWER if helps-

    Wednesday, September 23, 2009 7:11 AM

All replies

  • You are receiving 15023 error as you are restoring a MS SQL database from a backup. Keep in mind that when you perform backup and restore across the sql servers you are only restoring a user database and not the master database where logins are kept.

    You expect a restored database to be in exactly the same state as the backup, but the login fails for a user that had permissions in the backed up database.   This is caused by Security identification numbers (SID) that are mismatched or 'orphaned' in the sysusers table. 


    a)

    Use [YourDatabase]
    Go

    this will report all the orphaned login
    EXEC sp_change_users_login @action='Report';

    this should fix a orphaned user
    Exec sp_change_users_login @action='Update_One', @UserNamePattern= [orphaneduserDBUserName] , @LoginName=[orphaneduserloginname]

    b) or you can search for a script that can do this for all the orphaned users

        http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

           http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30834/ (I think this site requires you to register)

     

    Tuesday, September 22, 2009 1:41 PM
  • Hi,

    Explain the below mention points to understand the issue.

    PROD-
    MSSQL Server version
    MSSQL Server SP

    Dev-
    MSSQL Server version
    MSSQL Server SP

    Login Property 

    Windows Login
    SQL login.


    Microsoft reported as BUG for sql 2000 behaviour, Try it..
    http://support.microsoft.com/kb/819261
    User logons and permissions on a database may be incorrect after the database is restored
    http://support.microsoft.com/kb/168001

    BR
    Praveen Barath

    -Mark as ANSWER if helps-

    Wednesday, September 23, 2009 7:11 AM
  • Hi,

    Explain the below mention points to understand the issue.

    PROD-
    MSSQL Server version
    MSSQL Server SP

    Dev-
    MSSQL Server version
    MSSQL Server SP

    Login Property 

    Windows Login
    SQL login.


    Microsoft reported as BUG for sql 2000 behaviour, Try it..
    http://support.microsoft.com/kb/819261
    User logons and permissions on a database may be incorrect after the database is restored
    http://support.microsoft.com/kb/168001

    BR
    Praveen Barath

    -Mark as ANSWER if helps-

    • Proposed as answer by Jules Olave Wednesday, November 25, 2009 3:33 PM
    Wednesday, September 23, 2009 7:11 AM
  • You are receiving 15023 error as you are restoring a MS SQL database from a backup. Keep in mind that when you perform backup and restore across the sql servers you are only restoring a user database and not the master database where logins are kept.

    You expect a restored database to be in exactly the same state as the backup, but the login fails for a user that had permissions in the backed up database.   This is caused by Security identification numbers (SID) that are mismatched or 'orphaned' in the sysusers table. 


    a)

    Use [YourDatabase]
    Go

    this will report all the orphaned login
    EXEC sp_change_users_login @action='Report';

    this should fix a orphaned user
    Exec sp_change_users_login @action='Update_One', @UserNamePattern= [orphaneduserDBUserName] , @LoginName=[orphaneduserloginname]

    b) or you can search for a script that can do this for all the orphaned users

        http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

           http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30834/  (I think this site requires you to register)

     

    greetings:

    thank you Chirag_Shah for your help. you saved the day!!!

    Jules Olave
    Wednesday, November 25, 2009 3:36 PM