Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Prevent DDL Triggers from disabling


Prevent DDL Triggers from disabling

Author
Message
vivekkannan.k
vivekkannan.k
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 16
Hi friends ,


Here i am having SQL Server 2008 R2 Standard Edition . To Control the Schema level Changes, i have created a DDL Trigger, which is just Roll back the DDL Changes if they accrued from the users except sa .

in SSMS by right click the user can Disable that Database Triggers . i dont know how to prevent this DDL trigger.

Pls help me to resolve this .

Thanks

Vivek Kannan
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
only a sysadmin can disable the triggers, so as long as only you have sysadmin permissions, it's not a problem.

if other people have sysadmin,and you want to prevent them from disabling the trigger, you can't, unless you take away their sysadmin privileges.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
And if they have sysadmin permissions, you have larger problems than a DDL trigger, they could drop the database, shut down SQL, steal a backup, change data, etc.

Minimal permissions!


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


vivekkannan.k
vivekkannan.k
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 16
thx Lowell :-)

yes u r correct, users have Sysadmin permission .

Is there any way to Audit the Changes (delete , disable) on DDL Triggers .

The sys.triggers table has the flag value "is_disabled" .

monitoring this flag value changes will help to resolve this ?

Regards

Vivek Kannan
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
vivekkannan.k (4/4/2013)
thx Lowell :-)
yes u r correct, users have Sysadmin permission .
Is there any way to Audit the Changes (delete , disable) on DDL Triggers .
The sys.triggers table has the flag value "is_disabled" .
monitoring this flag value changes will help to resolve this ?
Regards
Vivek Kannan


I'll say it again, but I have the feeling you are afraid to make the change.
take away sysadmin permissions.
Minimum permissions is what they should have.
do it now.
add the logins as users to each of the databases they really need access to; and use an existing role create a new role that has the actual permissions they need in that database.

after that, there's no need to monitor anything, because noone can do anything behind your back; if they need to create something, then they can contact you, and you can review the item, suggest proper changes, or create it on their behalf.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vivekkannan.k
vivekkannan.k
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 16
Sure Lowell ,

its a live server . better i implement this on my development server first and then replicate the same on live .

will ping you if i experience any difficulty.

thanks for your help.


Regards

Vivek Kannan
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
holy crap! you have people with sysadmin permissions, on the Live server, Klutzing around and making DDL changes at will?

time to update your resume, it's only a matter of time before they do something that will cost you your job or worse, get you some jailtime.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search