Best way to migrate database(s) wihlst preserving object level permissions

Question

Post reply

Best way to migrate database(s) wihlst preserving object level permissions

kangarolf

Hall of Fame

Points: 3432
More actions
January 7, 2007 at 3:24 am

#115678

----SORRY THIS IS SQL 2000 not 2005!----

Hi all,

I'm looking for some advice about moving an SQL server to a new server that is not on the same domain or subnet (so network shares are right out!).

I initiall tried the shutdown, copy database files, attach at new server then sorted out the orphaned users (as the master database cant be copied). This copied the users and all the SPs over but no object level permissions. Even if I set the migrted user as a database owner the user still has no permissions on any tables/sps in the database.

Is there a way to preserve object level permissions..? Or to automate to some extent the re-granting of those permissions..?

Thanks for any advice

Rolf

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Are you migrating sql 2000 to 2000? or 2000 to 2005?

After restoring all user database or after attaching all user databases migrate the login using the script from the following article...and change the default dbs... you don't need to grant any permissions...

http://support.microsoft.com/kb/246133

MohammedU
Microsoft SQL Server MVP

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

Sugesh Kumar

One Orange Chip

Points: 27311

January 7, 2007 at 11:45 pm

#681708

best way that i would suggest is to use DTS to transfer your logins tothe destination servers.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

Minaz Amin

SSChampion

Points: 11052

January 8, 2007 at 12:21 am

#681714

Hi,

We had followed the following steps:

1. Restored the databases

2. Used the script in the KB 246133 to trasfer the logins

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

kangarolf Hall of Fame Points: 3432 More actions · Answer 4

Thanks for all the help.

I'm migrating from one windows2000 server running mssql2000 to a different server which is running mssql2000 on win2003.

I think some of my problems are that the second server is also running sql2005 and that it is running sql2005. I need to investigate service pack issues and things like that but incidentally what is the correct format for using a named instance in a connection string (off topic but someone might know) when you are using TCP/IP..?

data source=111.111.111.111/servername/instancename or

data source=111.111.111.111/instancename

Thanks again

Rolf

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 5

If you're talking about Windows logins, the login is identified by the SID that it gets from the domain. Even if the users have logins in both domains, the SIDs will be different there is no way for SQL Server to allow a Windows login to inheirit the object-level permissions of another Windows login. You could script the permissions on the old server, then edit the script to modify the user names and apply it to the new server.

With SQL logins, you should synchronize the SIDs of the logins on all your servers. Then when you migrate the database users are matched to the logins on the new server instead of being orphaned.

If users got permissions through database role membership then the roles should survive the migration and all you would have to do is to restablish role memberships in the new database. These could also be scripted from the old server.

MohammedU SSCoach Points: 19644 More actions · Answer 6

MohammedU

SSCoach

Points: 19644

January 8, 2007 at 12:59 pm

#681866

Instance should be used as...

ServerName OR IP address\InstanceName

If you have another instance on the server SERVICE packs will not effect each other...

MohammedU
Microsoft SQL Server MVP

Paul Kelsey SSC Veteran Points: 273 More actions · Answer 7

IMHO, if you're talking about two 2000 instances, the "cleanest" way to preserve your permissions during any migration is to restore a backup of master, (while in single-user mode) after restoring / re-attaching all user databases. When it comes back online afterwards, master knows all the SIDs and login mappings to all databases in the instance.

Too bad the same can't be said for a 2000 to 2005 migration, but there are better tools for that; as are nicley documented by previous posts.

Paul Kelsey SSC Veteran Points: 273 More actions · Answer 8

Paul Kelsey

SSC Veteran

Points: 273

January 8, 2007 at 5:51 pm

#681922

If anyone is going to attempt this in production, I forgot to mention that you'll want the target instance to be at the same, exact patch level - if possible. For example if old production is at 8.00.760 (SQL2K SP3), then bring the target up to 8.00.760 before restoring master.

kangarolf Hall of Fame Points: 3432 More actions · Answer 9

I did try doing that with the Master database but as the OS is different (2003 as opossed to 2000) and it also has 2005 installed I didnt hold out much hope! Indeed the instance refused to start up again =)

I think most of my problems are due to changes in Service packs between instances...I had neglected to check that. 2003 also seems to enforce some rules, such as TCP/IP being unavailable unless a certain service pack level is reached.

Thanks

Rolf

Paul Kelsey SSC Veteran Points: 273 More actions · Answer 10

Rolf,

Oh yeah - another thing to remember is that SQL Server 2000 RTM is pre-blaster. Server 2003 won't let you bring it up until it's at least Service Pack 3(a) (8.00.760).

To get around your problem, you have to add an alias in the network library to the RTM instance using Cliconfg.exe Run Cliconfg from Start > Run. The SQL Server Client Network Utility appears. Go to the Alias Tab. Click Add. Chose Named Pipes on the left. In the Server Alias textbox at the top of the same window, put the instance name. If's a named instance - put in the form of SERVERNAME\INSTANCENAME - the pipe name is built for you - click apply and close. - Looking for KB Article - http://support.microsoft.com/kb/815431 - GOT IT!. I ran into this working with clustered instances - but I think the same may apply to you. Hope this does it.

steve block SSCertifiable Points: 7698 More actions · Answer 11

One thing that might be helpful is to script out the database users and roles and Sql Server logins. Then you can save it to a script file, edit it and do global search and replace for a domain name.

Right-click the database - All Tasks - Generate SQL Script. On the Options Tab choose "Script database users and roles" and "Script SQL Server logins (Windows and SQL Server logins)". I would make sure that file format is not "International text". You can preview and create the script from the General Tab.

Steve

kangarolf Hall of Fame Points: 3432 More actions · Answer 12

Thanks for the massive amounts of help!

Its all sorted now...all my problems were due to clashes with the instances...Because both 2005 and 2000 were running at the same time I had to manually select a new port for the 2000 instances to run on as the programs werent clever enough when installing to sort that out themselves. So the weird permission errors I was getting was because I was trying to logon to the wrong set of DBs...it was getting routed to 2005 (The first to get installed) on the default port and not the 2000 DBs which were just being ignored. Changed the port 2000 was listening on, resynced the users and everything is good.

Now comes the challenge of getting all the DTS stuff migrated to 2005...bluergh