Mapping orphaned database users to SQL Server 2005 logins

Posted by p_lider October 4th, 2010

If you have many users defined in your MS SQL Server 2005 database, then if you will backup your database and then restore in a new, clean SQL Server instance, you will not be able to map database users with logins in your SQL Server instance. This is because of different SID numbers between the source and destination SQL Server instances. So even creating logins named exactly the same as users in a restored database will not help.

Fortunately, I found a solution. The SQL scipt below will map orphaned users in databases to logins in SQL Server instance even when their SIDs do not match:

sp_change_users_login @Action=’update_one’,
@UserNamePattern=’<database user name>‘,
@LoginName=’<sql server instance login name>‘
GO

Pings Off.

2 Responses

  1. bet365 italia says:
    10/06/2010 at 7:11 AM

    how are you!This was a really impressive post!
    I come from roma, I was luck to search your subject in yahoo
    Also I learn much in your subject really thank your very much i will come again

    Reply
    • p_lider says:
      10/06/2010 at 7:32 PM

      Hi! I am glad that this information was helpful for you. I am going to keep my blog updated with any recent problem, and solution, I face with. So if subjects like this interest you, then feel free to visit my blog anytime you want:)

      Reply