locked
Orphan Users RRS feed

  • Question

  • I moved my logins to new server and used Microsoft transfer login script, but some of the SQL logins can't authenticate. I need to run orphan user script for all databases once. I have a script to run for each database, is there anyway I can run for all databases in that instance?
    Monday, April 13, 2015 2:29 PM

Answers

  • You can use the below but it wont tell you which database it is... But it will report all orphan logins..

    sp_msforeachdb 'use [?] ; exec sp_change_users_login ''report'''
    Try this as well http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Monday, April 13, 2015 2:37 PM
  • If the issue is that the login fails, because they cannot access the default database, the issue is likely to be that the transfer script did not set the SID. The match much be on the SID.

    In that case, the best may be to drop the login and recreate them with the correct SID. (You cannot use ALTER LOGIN to change the SID.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by krotha Tuesday, April 14, 2015 3:05 PM
    Monday, April 13, 2015 9:18 PM

All replies

  • does the instance allow mixed authentication -- did you restart after making the changes --  it looks like more of authentication issue then orphaned users?? check this first

    can you run the code using

    sp_MSforeachDB 'use [?] your script code'

    example: sp_MSforeachDB 'use [?] select db_name()'


    Hope it Helps!!

    Monday, April 13, 2015 2:34 PM
  • You can use the below but it wont tell you which database it is... But it will report all orphan logins..

    sp_msforeachdb 'use [?] ; exec sp_change_users_login ''report'''
    Try this as well http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Monday, April 13, 2015 2:37 PM
  • Assuming that your instance allows mixed authentication...

    First step is to create the logins; you can do this manually (the preferred option) or you can google for sp_help_revlogin; note for this to be effective the collations on both Master databases need to be identical.

    Once you have created the logins you need to "connect" the logins within the databases to the logins you have created: -

    https://technet.microsoft.com/en-us/library/aa259633%28v=sql.80%29.aspx?f=255&MSPPError=-2147217396


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, April 13, 2015 2:38 PM
  • If the issue is that the login fails, because they cannot access the default database, the issue is likely to be that the transfer script did not set the SID. The match much be on the SID.

    In that case, the best may be to drop the login and recreate them with the correct SID. (You cannot use ALTER LOGIN to change the SID.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by krotha Tuesday, April 14, 2015 3:05 PM
    Monday, April 13, 2015 9:18 PM