locked
Trouble with SQL login RRS feed

  • Question

  • Dear all,

    I am restoring on a clean SQL server 2012 installation an SQL database backup coming from a previous SQL Server 2008 R2.

    That database backup comes with all users define in it. There is one particular SQL login named Report which has SLECT right on each table.

    Then that Report SQL login should also be able to connect to the database. As it is a fresh install, the user is not present in SQL security node\users. So I add it manually and set the corresponding database it needs to log to from the Mapping configuration tab.

    When applying changes, I get a message that this user is already existing for the database.

    How can I set the Login permission on my database , without dropping user right in it ?

    regards

    serge


    Your knowledge is enhanced by that of others.

    Tuesday, August 27, 2013 1:40 PM

Answers

  • Hello Serge,

    You have an "orphaned user", because the SID for the SQL login on the source and target server are different.

    See Secure Orphaned User AutoFix how to fix it.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by RohitGarg Tuesday, August 27, 2013 3:29 PM
    • Marked as answer by Serge Calderara Wednesday, August 28, 2013 9:05 AM
    Tuesday, August 27, 2013 1:54 PM
  • As stated above as well thats a orphen user, can fix it as below:

    use database_name
    go
    sp_change_users_login 'update_one','Report','Report'


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, August 27, 2013 3:27 PM
  • Since this is a SQL Login, you have to simply match the SID via

    sp_change_users_login 

    http://technet.microsoft.com/de-de/library/ms174378.aspx

    USE YourDB;
    GO
    EXEC sp_change_users_login 'Update_One', 'ReportUser', 'ReportUser';
    GO


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Tuesday, August 27, 2013 3:51 PM
  • Hello,

    You can refer to the online-book about Troubleshoot Orphaned Users:
    When we restore or attach a database to a different instance of SQL Server, Orphaning can happen if the database user is mapped to a SID which corresponding  SQL Server login that is not present in the new server instance.
    We can detect the orphaned users in current database by the run the following statement:
     sp_change_users_login @Action='Report';

    To solve the orphaned users, please refer to the solutions post above.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Wednesday, August 28, 2013 3:25 AM

All replies

  • Hello Serge,

    You have an "orphaned user", because the SID for the SQL login on the source and target server are different.

    See Secure Orphaned User AutoFix how to fix it.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by RohitGarg Tuesday, August 27, 2013 3:29 PM
    • Marked as answer by Serge Calderara Wednesday, August 28, 2013 9:05 AM
    Tuesday, August 27, 2013 1:54 PM
  • As stated above as well thats a orphen user, can fix it as below:

    use database_name
    go
    sp_change_users_login 'update_one','Report','Report'


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, August 27, 2013 3:27 PM
  • Since this is a SQL Login, you have to simply match the SID via

    sp_change_users_login 

    http://technet.microsoft.com/de-de/library/ms174378.aspx

    USE YourDB;
    GO
    EXEC sp_change_users_login 'Update_One', 'ReportUser', 'ReportUser';
    GO


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Tuesday, August 27, 2013 3:51 PM
  • Hello,

    You can refer to the online-book about Troubleshoot Orphaned Users:
    When we restore or attach a database to a different instance of SQL Server, Orphaning can happen if the database user is mapped to a SID which corresponding  SQL Server login that is not present in the new server instance.
    We can detect the orphaned users in current database by the run the following statement:
     sp_change_users_login @Action='Report';

    To solve the orphaned users, please refer to the solutions post above.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Wednesday, August 28, 2013 3:25 AM
  • thanks to all of you it works fine

    regards

    serge


    Your knowledge is enhanced by that of others.

    Wednesday, August 28, 2013 9:05 AM