x

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?
more ▼

asked Dec 12 '11 at 01:39 AM in Default

kautuk gravatar image

kautuk
11 1 1 1

Can you please add details that how are they "CREATING REPLICATIONS"?
Dec 12 '11 at 02:32 AM Usman Butt
But does that matter in any way? They may create replications via the GUI or using scripts...
Dec 12 '11 at 02:40 AM kautuk
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?
Dec 12 '11 at 03:51 AM Usman Butt
Does the users have db_owner rights (the minimum for various parts of replication) on the DB?
Dec 12 '11 at 07:00 AM Usman Butt
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.
Dec 12 '11 at 08:18 PM kautuk
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x31
x21

asked: Dec 12 '11 at 01:39 AM

Seen: 1026 times

Last Updated: Dec 12 '11 at 01:39 AM