locked
SQL Server ( Orphan users mapped with Domain level account ) RRS feed

  • Question

  • Dear All,

    I have some orphan users in Mydatabase & I am able to map with SQL Server Aunthication login........

    can I map orphan user with Windows level Aunthication account ?

    sp_change_users_login 'update_one' , 'test' , 'Windows\XXX'

    error
    ===========

    Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131

    Terminating this procedure. The Login name 'Windows\XXX' is absent or invalid.

    ============

    but login is there.........

    reagrds
    ravi




    india
    Friday, October 2, 2009 7:05 AM

Answers

  • Hi  Ravi,

    As mentioned in the thread sp_change_users_login cannot be used with windows principals.

    Is this windows\ravi a local system account or  a domain account ??

    If this is a domain account,You should probably do this to overcome the problem that you are facing.

    1. Open the new database's security tab and go to USERS , locate ravi and drop that user.
    2. Now right click the users tab and new user creation tab will pop up , now try adding RAVI user and select WINDOWS AUTHENTICATION and map the windows\ravi and add him under the role that you would like to.

    If this doesnt seem to work ,
    get us the output for select * from sys.server_principals and select * from sys.database_principals (run on that restored db)

    and also check whether the principal WINDOWS\ravi exists at both win and sql level.
    Thanks, Leks
    • Proposed as answer by Alex Feng (SQL) Monday, October 5, 2009 8:00 AM
    • Marked as answer by MSSQL DBA Thursday, October 8, 2009 1:49 PM
    Saturday, October 3, 2009 7:22 PM

All replies

  • Hello Ravendra

    According to BOL:
    sp_change_users_login cannot be used with a SQL Server login created from a Windows principal

    http://msdn.microsoft.com/en-us/library/ms174378(SQL.90).aspx

    Since the Windows Authentication cannot become orphaned - it is linked via the SID. It will automatically link up to the Windows login where it exists.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Friday, October 2, 2009 9:30 AM
  • Did u try to add the windows login in to sql server using sa or other sql logins ?
    If yes ,what error does that step give you ?
    Thanks, Leks
    Saturday, October 3, 2009 6:52 AM
  • lekss,
    I have followed below procedure..

    1. I have restore database from server1 to server2.
    2. when i check with sp_change_users_login 'report' found 1 orphan user (ravi) 
    3. now i want to fix with windows\ravi windows account this account having sysadmin role & not mapped with new restore database .

    when i run sp_change_users_login 'update_one' , 'ravi' , "windows\ravi" or 'windows\ravi'

    error:-

    [Terminating this procedure. The Login name 'windows\ravi' is absent or invalid.

    Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131
    ================

    but login is there.........

    regards
    ravi


    india
    Saturday, October 3, 2009 7:18 AM
  • Hi  Ravi,

    As mentioned in the thread sp_change_users_login cannot be used with windows principals.

    Is this windows\ravi a local system account or  a domain account ??

    If this is a domain account,You should probably do this to overcome the problem that you are facing.

    1. Open the new database's security tab and go to USERS , locate ravi and drop that user.
    2. Now right click the users tab and new user creation tab will pop up , now try adding RAVI user and select WINDOWS AUTHENTICATION and map the windows\ravi and add him under the role that you would like to.

    If this doesnt seem to work ,
    get us the output for select * from sys.server_principals and select * from sys.database_principals (run on that restored db)

    and also check whether the principal WINDOWS\ravi exists at both win and sql level.
    Thanks, Leks
    • Proposed as answer by Alex Feng (SQL) Monday, October 5, 2009 8:00 AM
    • Marked as answer by MSSQL DBA Thursday, October 8, 2009 1:49 PM
    Saturday, October 3, 2009 7:22 PM
  • thanks its working..............

    india
    Thursday, October 8, 2009 1:49 PM
  • Droping that oit\xxx user and reassigning permissions works well - thanks it helped me too
    Vijay Bhasker Reddy CH.
    Wednesday, December 14, 2011 8:30 AM