Skip to main content

 none
Security related question RRS feed

  • Question

  • Hi All,

    This is Security related question. Please help.

    Is the below command equivalent to EXEC sp_change_users_login 'Update_One', 'dbuser', 'login';    ?

    USE [DatabaseName]
    ALTER USER [DbUserName]
    WITH LOGIN = [LoginName]

    if so, from which version MICROSOFT has changed this ? does both commands use to fix a orphan user?

    Thanks,

    Sam

    Tuesday, October 15, 2019 6:36 AM

Answers

  • But, why does SID mismatch happens? dropping a login and re-creating it ?
    It happens because when you create new login to match with user it takes its own SID as provided by master database. Whenever you create new login every time SQL Server provides new security identified (SID) so when you move database on other machine and if you are not moving login and cerating a new one login will be created using NEW sid which will not match old SID of login on old server hence the whole drama, clear ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Samantha v Tuesday, October 15, 2019 2:39 PM
    Tuesday, October 15, 2019 2:12 PM
    Moderator

All replies

  • If I am correct the first command is used to fixed orphaned user with login it was mapped before you migrated to current server. The second one is remapping it to other login a very fine difference but in latter case SID changes ( it matches to current login you are mapping it to)  but in former it does not. I have not tried though

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, October 15, 2019 7:07 AM
    Moderator
  • Thanks Shanky.  A follow up question, what are possible reasons why we see SID mismatches between logins and db users. Recently, we have migrated a sql server instance from on-prem to Azure IaaS sql instance. Found out, many such orphan users. When users complained, we had to fix manually and give the permissions manually.

    Out of curiosity, we went back to the old server and checked for orphan users in each database, surprisingly we found same orphaned users. Then came to conclusion that same permissions and orphan users are migrated while restoring backups. So looks like even before we are supporting this server, orphan users are existing in those db's. During migration, we script out logins, using sp_revlogin sp and restored the db's , jobs etc. As db permissions comes along with the database backup, we were under the impression that everything was fine until testing team found out that they are unable access db using some logins. Looks like they using those logins at all in the old server. since migration happened, they are now testing end to end.

    So, to avoid such things in future migrations, want to know what could result in orphan users??


    • Edited by Samantha v Tuesday, October 15, 2019 10:53 AM added missing comments
    Tuesday, October 15, 2019 10:53 AM
  • Thanks Shanky.  A follow up question, what are possible reasons why we see SID mismatches between logins and db users. Recently, we have migrated a sql server instance from on-prem to Azure IaaS sql instance. Found out, many such orphan users. When users complained, we had to fix manually and give the permissions manually.

    There are 2 things here

    1. Windows Login: This has same SID as SQL Server does not controls this login, this login is created at windows/AD level

    2 SQL Server login: SQL Server maintains these logins has SID for each login which is different, so when you restore database on other server users move but logins do not. You need to create this login by using sp_help_rev_login SP so that SID does not change and you can then easily map the user to old login. One exception is SA which has same SID no matter what.

    So, to avoid such things in future migrations, want to know what could result in orphan users??

    Even after migration using sp_help_rev_login you would have to map the user with login this is required for SQL Server logins not the windows login. As an answer you can write your own script to avid this but there is no inbuilt thing to avoid this on the fly


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, October 15, 2019 11:08 AM
    Moderator
  • But, why does SID mismatch happens? dropping a login and re-creating it ?
    Tuesday, October 15, 2019 12:02 PM
  • But, why does SID mismatch happens? dropping a login and re-creating it ?
    It happens because when you create new login to match with user it takes its own SID as provided by master database. Whenever you create new login every time SQL Server provides new security identified (SID) so when you move database on other machine and if you are not moving login and cerating a new one login will be created using NEW sid which will not match old SID of login on old server hence the whole drama, clear ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Samantha v Tuesday, October 15, 2019 2:39 PM
    Tuesday, October 15, 2019 2:12 PM
    Moderator
  • Thanks :)
    Tuesday, October 15, 2019 2:39 PM