DDL Trigger to prevent dropping of a specific table?

Question

DDL Trigger to prevent dropping of a specific table?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--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!

Eric M Russell SSC Guru Points: 125564 More actions · Answer 2

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! 😛

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

andyobl Right there with Babe Points: 767 More actions · Answer 3

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. 😎

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

HI Lowell,

Please suggest how to capture DML operation here.

Thanks

Sourav

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 5

SQL-DBA-01 (4/10/2015)
HI Lowell,
Please suggest how to capture DML operation here.
Thanks
Sourav

there is no DML in a DDL trigger...but there is a command text.

if you mean command from the DDL trigger that is being called, it's in the xml.

go back to my trigger example and find this line:

@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

you can then use it elsewhere, since it's in a variable.

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!