detach/attach loses permissions

Hi, I was wondering if anyone had insight on this. I have traditionally
always used detach/attach as a method of copying databases i.e.:
I have 2 servers running SQL 2000: serverA, serverB
My intention is to copy a database (call it DB1) from server1 to server2
I perform the following:
1. i detach DB1
2. copy it (call it DB2)
3. attach DB1 to Server1 and DB2 to server2
4. place both server1 and server2 in single user mode via the -T3608
parameter
5. detach model THEN msdb from Server1, copy them and move the copies to
server2
6. attach model THEN msdb to server1 and server2
7. remove the -T3608 parameter and restart both sql services
8. script out the logins on server1 and apply them to server2
Now that is all fine according to microsofts article:
http://support.microsoft.com/defaul...kb;en-us;224071
However what i have noticed at times is that on server2, the logins are
missing permissions to DB2. Why does this happen?
Can things go missing if i perform it in this order:
1. place both server1 and server2 in single user mode via the -T3608
parameter
2. detach model THEN msdb from Server1, copy them and move the copies to
server2
3. attach model THEN msdb to server1 and server2
4. remove the -T3608 parameter and restart both sql services
5. i detach DB1
6. copy it (call it DB2)
7. attach DB1 to Server1 and DB2 to server2
8. script out the logins on server1 and apply them to server2
?
Missing permissions occured when i did the above for our greatplains
database.
Any help appreciated.
Cheers, johnSQL Server Logins are mapped to DB Users via an ID that can be different on
both servers, especially when they are not recreated in the exact order on
both servers. You need to use sp_change_users_login to fix the mapping
after the attach and after you recreate the logins. These links might be of
help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23JAspriBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> Hi, I was wondering if anyone had insight on this. I have traditionally
> always used detach/attach as a method of copying databases i.e.:
> I have 2 servers running SQL 2000: serverA, serverB
> My intention is to copy a database (call it DB1) from server1 to server2
> I perform the following:
> 1. i detach DB1
> 2. copy it (call it DB2)
> 3. attach DB1 to Server1 and DB2 to server2
> 4. place both server1 and server2 in single user mode via the -T3608
> parameter
> 5. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 6. attach model THEN msdb to server1 and server2
> 7. remove the -T3608 parameter and restart both sql services
> 8. script out the logins on server1 and apply them to server2
> Now that is all fine according to microsofts article:
> http://support.microsoft.com/defaul...kb;en-us;224071
> However what i have noticed at times is that on server2, the logins are
> missing permissions to DB2. Why does this happen?
> Can things go missing if i perform it in this order:
> 1. place both server1 and server2 in single user mode via the -T3608
> parameter
> 2. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 3. attach model THEN msdb to server1 and server2
> 4. remove the -T3608 parameter and restart both sql services
> 5. i detach DB1
> 6. copy it (call it DB2)
> 7. attach DB1 to Server1 and DB2 to server2
> 8. script out the logins on server1 and apply them to server2
> ?
> Missing permissions occured when i did the above for our greatplains
> database.
> Any help appreciated.
> Cheers, john
>|||Logins are in master table and you dont have copied it to other server
Use sp_help_revlogin by microsoft to transfer login
http://support.microsoft.com/default.aspx?kbid=246133
OR
If login already exists on Server 2 then you need to map user to logins
in the DB2., since the SIDs are different on both the servers for the
login.
Regards
Amish|||thanks for the help Andrew and Amish.
I have always used the following article as my approach to scripting out the
logins:
http://www.support.microsoft.com/?id=246133
This has worked in the past.
The strange thing (i.e. when this was performed on the Greatplains database)
was that when i reattached all the databases back onto Server1 (and of
course the copies on server2), permissions went missing on server1 as well.
Is this particular to greatplains?
any help appreciated.
cheers, john
"amish" <shahamishm@.gmail.com> wrote in message
news:1135174050.693957.5380@.g14g2000cwa.googlegroups.com...
> Logins are in master table and you dont have copied it to other server
> Use sp_help_revlogin by microsoft to transfer login
> http://support.microsoft.com/default.aspx?kbid=246133
> OR
> If login already exists on Server 2 then you need to map user to logins
> in the DB2., since the SIDs are different on both the servers for the
> login.
> Regards
> Amish
>|||I am not quite sure what you did but have you tried the
sp_change_users_login to see if that helps?
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:ujPILOkBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> thanks for the help Andrew and Amish.
> I have always used the following article as my approach to scripting out
> the logins:
> http://www.support.microsoft.com/?id=246133
> This has worked in the past.
> The strange thing (i.e. when this was performed on the Greatplains
> database) was that when i reattached all the databases back onto Server1
> (and of course the copies on server2), permissions went missing on server1
> as well.
> Is this particular to greatplains?
> any help appreciated.
> cheers, john
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1135174050.693957.5380@.g14g2000cwa.googlegroups.com...
>