Unanswered: Replication - How to create objects as dbo.object when not SA
I just completed creating a replication job between a server at my location, where I have SA privileges, to a subscription server at a location where I have db_owner rights (no SA). When replication runs, the objects get created on the subscriber as userid.object rather then dbo.object. How do I cause replication to create the objects as dbo.object?
Here is some additional information. The subscriber server is at a remote location managed by another IT department. I connected to the subscriber with a user id that was created in the role db_owner. The security was setup in Enterprise Manager by clicking on Replication and then right clicking on Publication. From there I chose "Configure Publishing, Subscibers and Distribution..." and then the Subscribers tab. For the subscriber I chose "Use SQL Server Authentication" and entered my dbo user id. Since I do not have SA rights on this server I need a way to have my dbo id created objects as dbo.objectname.
When you create the publication (through 'Publication Properties' in SQL-EM) you can specify the owner of the articles to be created in the subscription database. You do this with the 'Article Defaults' button on the 'Articles' tab. Enter dbo in destination table owner field (or destination stored procedure owner field for stored procedures). If you leave these fields blank then the destination objects will be created with the current user as their owner.
Shortly after I submitted my question I discovered exactly what you indicated, but I found that SQL Server is creating its own stored procedures (sp_MSDel_tablename, sp_MSIns_tablename) using my userid and not dbo. I noticed the Commands tab lists these stored procedures for each table. I assume this is the place where I must prefix each procedure with dbo.. Does this sound correct or is there another way to address SQL Server created procedures?