How do I get Copy Database Wizard to work?

This is SO frustrating. Go through the wizard selecting the db's I want to pull down from production, gives me an option to get logins (great), but only gives option to pull down stored procedures in Master db (I am a DB owner on these database btw), when I execute it creates the database, and the logins, but doesn't pull down any of the tables or the stored procedures or views unless they were ones I created recently. It's like theres some wierd permissions thing, but like I said, I'm a DB on these databases.

Try using Copy Database Wizard (CDW) in the Community Technology Preview for SP2. It has not released yet, but check www.microsoft.com/sql/ for it in the coming months. Many issues were fixed including areas like login/user SID orphaning, permissions and roles. It is hard to tell if we fixed your specific issue, although I have not seen cases where tables, views or SPs inside the database being transferred did not copy in the way you describe. There was also an issue prior to SP2 of trying to copy objects which were already in the model database on the destination server instance.

If you see the problem after trying the CTP, please open a defect against it and include:

1. sufficient T-SQL scripting or attach the .mdf file of a minimal case.
2. copy/paste the Summary page just before the transfer begins with the details of the run.
3. any relevant error lines from the Windows Event Application Log (just the error ones is fine).
4. the <MyDocumentsAndSettings>\Local Settings\Application Data\Microsoft\SQL Server\Smo\TransferDump*.sql files. There will be one file for the Extra Server Objects, and optionally one file for each database if the Smo method of transfer was used.

Hope this helps,

Ed Dudenhoefer
Microsoft SQL Server