restoring database onto a different machine

Hi,
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
Andy
I suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>
|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>
|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is[vbcol=seagreen]
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
D.[vbcol=seagreen]
other[vbcol=seagreen]
PITA.[vbcol=seagreen]
am
>
|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> is
> change
> D.
> other
> PITA.
> am
>
|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and[vbcol=seagreen]
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
restore[vbcol=seagreen]
get[vbcol=seagreen]
that[vbcol=seagreen]
login[vbcol=seagreen]
a[vbcol=seagreen]
on[vbcol=seagreen]
database[vbcol=seagreen]
I[vbcol=seagreen]
right[vbcol=seagreen]
to[vbcol=seagreen]
I
>