December 12, 2011 at 2:50 am
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?
Thanks in advance !
December 12, 2011 at 3:12 am
You don’t need triggers for it. If you don’t want somebody to do (or not to do) something, just don’t GRANT him that permissions.
December 12, 2011 at 3:41 am
But what about replications? The users should have privileges to create replications so they will be effectively creating procs.
December 12, 2011 at 3:53 am
kautuk (12/12/2011)
But what about replications? The users should have privileges to create replications so they will be effectively creating procs.
Replication is something precisely done by DBAs only. So what is the problem if production server DBA has those rights ?
Yes, you should be much worried if developers are entitled to do the replication :w00t:
December 12, 2011 at 4:05 am
Yeah, you are right but all those things are figured out already. Only the DBAs are allowed to create replications and nobody is talking about production servers here. So, now is there any way out to allow them to create replications and deny them the permissions to create procs ?
December 12, 2011 at 5:56 am
kautuk (12/12/2011)
Yeah, you are right but all those things are figured out already. Only the DBAs are allowed to create replications and nobody is talking about production servers here. So, now is there any way out to allow them to create replications and deny them the permissions to create procs ?
As one needs db_owner permission on the database to publish it, I think you can't restrict them from creating procedures, however for your auditing you can go ahead to create DDL triggers to catch the CREATE_PROCEDURE event, & send the session information to you in mail.
December 12, 2011 at 6:01 am
Divine Flame (12/12/2011)
kautuk (12/12/2011)
Yeah, you are right but all those things are figured out already. Only the DBAs are allowed to create replications and nobody is talking about production servers here. So, now is there any way out to allow them to create replications and deny them the permissions to create procs ?As one needs db_owner permission on the database to publish it, I think you can't restrict them from creating procedures, however for your auditing you can go ahead to create DDL triggers to catch the CREATE_PROCEDURE event, & send the session information to you in mail.
I am still not comfortable with what kautuk said. If it's not production box it's OK but have you heard about 'Development DBAs'? They should manage Replication & DEV team shouldn't touch it (unless & until you belong to a small shop & it follows ‘One Man for All’ concepts).
December 12, 2011 at 6:40 am
Dev (12/12/2011)
I am still not comfortable with what kautuk said. If it's not production box it's OK but have you heard about 'Development DBAs'? They should manage Replication & DEV team shouldn't touch it (unless & until you belong to a small shop & it follows ‘One Man for All’ concepts).
Agreed. I was amazed after seeing this interesting requirement that one can configure the replication, but should not be able to create procedures! :hehe:
December 12, 2011 at 9:27 pm
Yeah, I agree that its a curious case. But we have a requirement that the Developer DBAs should not be allowed to create objects on a particular database but someone can create replications to and from this database. Can i have some suggestions please.
BTW, its not a shop but a supermarket :w00t:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply