As good as the Database Publishing Wizard is, it has some limitations. It can't handle procedures or UDFs set as ExecuteAsUser (the offending clause in SQL is: WITH EXECUTE AS 'dbo'), and these objects are often found in ASP.NET 2.0 databases that are used with the VS 2005 Diagramming support that lets you make changes to DB objects visually. If youíre willing to give up the diagramming support from the source DB, you can drop the sysdiagrams table, and the related objects from your source DB before you run the wizard:
fn_diagramobjects (Functions, Scalar Valued)
sp_alterdiagram (Stored Procedure)
sp_creatediagram (Stored Procedure)
sp_dropdiagram (Stored Procedure)
sp_helpdiagramdefinition (Stored Procedure)
sp_helpdiagrams (Stored Procedure)
sp_renamediagrams (Stored Procedure)
sp_upgraddiagrams (Stored Procedure)
Before creating a SQL script from a source DB you should also delete some of the old records you donít really want to copy. You should empty the aspnet_WebEvent_Events table first, and you may want to remove your anonymous users, and perform other maintenance like that first.
delete from aspnet_WebEvent_Events
delete from aspnet_profile
where userid in
(select userid from aspnet_users where isanonymous = 1)
delete from aspnet_users where isanonymous = 1
IMPORTANT! Make sure that you have deleted any testing administrator accounts before you deploy to a production server (such as the common admin/admin account). You must have a good admin account with a known and somewhat secure password because the SQL generated by the publishing wizard will delete the entire existing target DB and re-create it using only the data present in the source DB at the time this script was created.
Please try this Wizard - I'm sure you'll like it.