Server / Database triggers

Here is my development teams, there are some naughty developer doing something bad things by themselves like create db, create jobs....etc~ some even know the sa password

apart from blocking/disable sa, change sa password, am I able to create DDL trigger on SERVER, which this trigger can only modify/delete by the creater , not SA

February 18th, 2015 2:57am

It depends on the permissions which the developer do have. If they have SysAdmin permissions, then there is no way to protect Server/DB Triggers or other object. If they have not SysAdmin role, then you could explict deny alter permissions on that Trigger for all other users then DBA.

Free Windows Admin Tool Kit Click here and download it now
February 18th, 2015 3:21am

There is nothing to stop sa from doing anything.

While DDL triggers can be good to audit what is going on at the server and in the databases, they are not good for preventing actions. Particularly, one must understand that they fire after the action. A DDL trigger to prevent indexes being created during office hours is entirely contraproductive.

From what you describe, you need to tighten security. Rename sa, change the password, write it down and lock it into a safe.

And for the rest, be considerate with the permissions you hand out. Although, there is always the problem that too much security goes in the way, and this is why auditing is a good way. But for auditing to work, you need to get sa and all other anonymous accounts out of the way, so that everyone can he held accountable for their actions.

February 18th, 2015 3:23am

I agree to what Erland suggests.

You need to change the sa password and provide each developer with individual windows accounts. You can even give sysadmin permissions to those accounts. But then you audit and you know what exactly each person does.

But as long as they are all using 'sa' you cant stop them from doing anything nor you will be able to say who did what. And this is exactly the reason why we should not encourage the use of SQL Authentication logins unless its really necessary and Windows Authentication is not pos

Free Windows Admin Tool Kit Click here and download it now
February 18th, 2015 6:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics