SQL Server Performance

Replication of Stored Procedure changes permission SQL server 2008

Discussion in 'SQL Server 2008 General DBA Questions' started by Manoj118, Feb 15, 2010.

  1. Manoj118 New Member

    Hi
    I want to replicate my stored procedures to development environment (both servers are SQL server 2008) . but when I replicated my stored procedures to development database. DDL admin in published database is not able to modify the stored procedure. while modifying procedure error message comes " Procedure sp_MSreplcheck_publish, Line 16 Only members of the sysadmin or db_owner roles can perform this operation.". Alter permission from ddl admin are revoked automatically after replication. What should I do to given him alter permission to a user without elevation him to dbo.
    Manoj118, Feb 15, 2010
    #2

  2. satya Moderator

    How frequently you need to change the SP code?
    You could take help of EXECUTE AS in thsi case to do the same in order to avoid the user to take any privilege.
    satya, Feb 21, 2010
    #3

  3. MohammedU New Member

    What are you benifitting by replicating the procedure? simply you can write a script to generate the proc script and schedule it to run on dev server.
    MohammedU, Apr 2, 2010
    #4

Share This Page