SQL Server ( Orphan users mapped with Domain level account )

_{Friday, October 2, 2009 7:05 AM}

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

_{Saturday, October 3, 2009 7:22 PM ✅Answered}

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

_{Friday, October 2, 2009 9:30 AM}

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.

_{Saturday, October 3, 2009 6:52 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 7:18 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

_{Thursday, October 8, 2009 1:49 PM}

thanks its working..............
india

_{Wednesday, December 14, 2011 8:30 AM}

Droping that oit\xxx user and reassigning permissions works well - thanks it helped me tooVijay Bhasker Reddy CH.