Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DDL Trigger to prevent dropping of a specific table? Expand / Collapse
Author
Message
Posted Monday, July 18, 2011 6:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
If they have sysadmin rights, they can disable the triggers and then drop the table anyway. That negates the whole point of what you're trying to do.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1143376
Posted Tuesday, July 19, 2011 2:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:28 AM
Points: 1,660, Visits: 4,750
upstart (7/14/2011)
Well, the reason I am worrying about this one specific table being dropped is because it is used to store information from another DDL trigger we have setup on the database that monitors all database actions.

Last week, one of our developers dropped every table/proc/view from the dbo. schema on our development box and either covered their tracks really, really well or our default 'black box' trace for some reason did not capture anything regarding it. We only lost a day of coding since we had a full backup from 1am to restore from. Since then, we implemented 1 DDL trigger to put all database events into a table and want to prevent the developers from dropping it.

So, in essence, if someone decides to drop everything again, at least we will still have this table to tell us what happened. Hmmmm.....unless they delete everything from the table? I may have to think about how to handle that issue as well.

Edit: decided on putting a normal table trigger on this table to prevent deletions. Also, modified the original DDL trigger to not log anything to do with Updating Statistics since it creates about 400 records a night that really serve no purpose and added a weekly job to remove records older then 7 days.

....Auditing...fun for the whole family!

Consider moving your audit tables to a separate database, remove the developers from SYSADMIN, and then add them as members of DBO on the existing database. The problem with SYSADMIN is that it grants them permission to do all sorts of things beyond just creating and dropping objects in a specific database.
Start out by making a copy of the audit tables into the new audit database, create a new test user with same membership as developers currently have, and experiment with the test user until it's all working as needed. Only then alter the existing tables and users.
Post #1144466
Posted Tuesday, April 23, 2013 9:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:15 AM
Points: 21, Visits: 101
Slightly confused here... I tried this on my TEST server and I cannot drop any tables.

When I try to drop the [cherries] table, I get the following message:-

DROP TABLE Issued.

(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure safety, Line 8
Tables cannot be dropped in this database.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Post #1445541
Posted Tuesday, April 23, 2013 10:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
Andy did you copy the trigger verbatim or modify it a bit?

it worked fine for me when i posted to the thread originally;

can we see your version of the trigger, just to be sure?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1445554
Posted Tuesday, April 23, 2013 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:15 AM
Points: 21, Visits: 101
Crap. Sorry am being stupid. Ignore my previous post.

Anyways, I am hoping to use your script as a starting point to prevent circa 400+ tables from being dropped. I have tried a few things but I am not how to pass the table values to @oname?

Any ideas appreciated. Thanks.
Post #1445557
Posted Tuesday, April 23, 2013 10:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
well, note the comments in the thread;
if you need to protect 400 tables, it's probably better to look at the security aspect of it, adn not tackle this via a DDL trigger.
however, the modification should be fairly simple:

first, you need a table with the list of protected tables:
create table ProtectedTables(TableName varchar(200) NOT NULL)

INSERT INTO ProtectedTables(TableName) Values ('ProtectedTables')
--make sure everyone has access to "see " the data in the table, to avoid the trigger crashing due to lack of permissions on this table
GRANT SELECT ON ProtectedTables TO PUBLIC


AND finally, you need to change one line of code in the trigger example to select from the new table, instead of a list:
IF @oname IN ('tblBananas','tblApples','tblOranges')
--should now be
IF @oname IN (SELECT TableName FROM ProtectedTables)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1445577
Posted Tuesday, April 23, 2013 11:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:28 AM
Points: 1,660, Visits: 4,750
If you're struggling with a group of users, or a 3rd party application, that insists on connecting to the server via the SA "system administrator" account, then there is a way to fake them off. You can re-name the 'SA' account, and then re-create 'SA' using more a more moderate roles and permissions of your choosing. For more advanced users who need to do things like script out objects, query DMVs, or run profiler traces, you can also add VIEW DEFINITION and VIEW SERVER STATE.

For example:

use MASTER
go
alter login sa DISABLE;
go
alter login sa with name = [sa_bak];
go
create login sa with PASSWORD = 'Just%nt%me', DEFAULT_DATABASE = master;
go

use Accounting;
go
drop user sa;
go
create USER sa for login sa;
go
exec sp_addrolemember 'db_datareader', 'sa';
exec sp_addrolemember 'db_datawriter', 'sa';
go

Developer "We need to connect to the server as sysadmin."

DBA: "I don't know about that. Are you guys going to mess with the server settings or start altering objects?"

Developer "Uhh.. No, we just need access to everything."

DBA: "OK, you seem like a nice enough guy, so I'll set it up for you."

LOL!
Post #1445604
Posted Wednesday, April 24, 2013 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:15 AM
Points: 21, Visits: 101
Hello,
There's a long winded reason why that is difficult to implement. Anyway, your suggestion with the trigger worked on a small set of test data. So thanks very much for you help.
Post #1445912
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse