Hello,

when there is need to transfer full backup from one server to another
server, there is usually need to transfer logins too.
backup contains users. So when we restore backup to another server, we have
our users in it.

Let say we have user1 in datebase1

Now we create login user1 and try to map that login to database1

But now we got error, something like 'User, group or role 'user1' already
exists in the current database ...'

So we need to delete user1 from database1 and then map it again.
Problem is if there is many objects depend on user1, we must break this
connection first, and then we can delete user1.

Is there any different way, remapping or somthing?
What to do in this case?

Regards

On Jan 20, 5:06*am, "m" <web12mas... (AT) gmail (DOT) com> wrote:
See the Books Online for procedure sp_change_users_login which will
map a database user to an instance user.

HTH -- Mark D Powell --

m (web12master (AT) gmail (DOT) com) writes:
ALTER USER User1 WITH LOGIN User1


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

You can use sp_change_users_login

The first time you restore the backup on another server you have to
crate the login for your database user:
EXEC sp_change_users_login 'Auto_Fix', 'User1', Null, 'user1password'
GO

After this Login User1 exists on the new server.
Now you can use a shorter version to reconnect User and Login:
EXEC sp_change_users_login 'Auto_Fix', 'User1'
GO

For an overview try:
EXEC sp_change_users_login 'Report'
GO

Look at online-help for all informations ...
http://msdn.microsoft.com/en-us/library/ms174378.aspx

Thank you guys.

If I understood good, Microsoft recommend us to use ALTER USER instead
sp_change_user_login
But I got syntax error if I use ALTER USER (I don't what I'm doing wrong,
command is pretty straight forward) and sp_change_user_login works fine.
I solved the problem with sp_change_user_login

Thanks again,

cheers

"Lutz Uhlmann" <news (AT) invalid (DOT) invalid> wrote

m (web12master (AT) gmail (DOT) com) writes:
What does "SELECT @@version" say? ALTER USER to change the login was added
in SQL 2005 SP2. You are not stuck on SQL 2005 RTM are you?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

select @@version gives:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 3)

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

m (web12master (AT) gmail (DOT) com) writes:
That is SQL 2005 SP2, so you are two service packs behind. SP4 was
released just before Christmas. But ALTER USER SET LOGIN should work
for you.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx