Przemysław Łukawski's blog » SQL Server http://p-lider.lideve.eu/wordpress Solutions for IT technical problems admins and developers may face with. Tue, 23 Sep 2014 18:29:49 +0000 en-US hourly 1 https://wordpress.org/?v=4.2.22 Mapping orphaned database users to SQL Server 2005 logins http://p-lider.lideve.eu/wordpress/?p=93 http://p-lider.lideve.eu/wordpress/?p=93#comments Mon, 04 Oct 2010 10:51:49 +0000 http://p-lider.rootnode.net/wordpress/?p=93 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

]]> http://p-lider.lideve.eu/wordpress/?feed=rss2&p=93 2