asked
Exempting replication procedures from DDL triggers
Hi, I need to create DDL triggers to prevent any user from creating procedures/functions in my database. But a trigger for 'CREATE_FUNCTION,CREATE_PROCEDURE' prevents the users from creating any replications also. The 'NOT FOR REPLICATION' clause is for DML triggers only, I guess. Can anyone suggest me a way to allow users to do replication things and disallow them to create any procedures/functions?
Can you please add details that how are they "CREATING REPLICATIONS"?
I cannot understand your question then. If I understand correctly, you want them to create replications. right? And AFAIK, there are already system procedures available through which the replication is done. So add the detail where it is getting wrong? Any specific error/s you are getting?
Does the users have db_owner rights (the minimum for various parts of replication) on the DB?
Yes, they have db_owner rights. The problem occurs when a user wants to create a new replication. As some system procedures are created during the process, it fails because users are not allowed to create procedures. So what I want is a way to block users from creating any new objects but they can create replications.
This is a very strange but interesting situation. Sorry I did not have the time to test and Replication is configured by sysadmin in our environment. So Can you please point out any specific system procedure details created? Then may be I can guide you to some alternative. One more point, Since they are the db_owner, can't they disable the database trigger? But this is a separate thing
It is failing to create 'sp_MSins_tablename' etc. as we have the trigger to block the creation of procedures. I can think of a way that checks the existence of 'sp_MS%' in the procedure name to be created. If so the trigger may allow it to be created using an IF clause. Is there any other way out? They can disable the trigger but we dont want them to.
I guess this sp_MS... approach would be vulnerable to the issue you specified earlier. But since these are system procedures, so they most probably wold be created in sys schema. I hope this hint would be enough to get you going. (Sorry time constraints is not letting me dive deep into it).
