how to protect the table and watching for it

Question

how to protect the table and watching for it

osama.g.y.a

SSC Rookie

Points: 34
More actions
December 6, 2009 at 6:34 am

#385898

We have a table in the SQL database name Alies This table note of each period of the disappearance of all its contents
We do not know what is the reason??!
For your information the table is not deleted, but I think it is the work of Truncate Table
As is well known that this is his job to delete records in the table
Is there a way to protect this table and monitor changes that occur upon ???!!
And prevent any person from the work to Truncate this table
Could you please help me writing the command for restricting the table to be truncated for all users

Viewing 7 posts - 1 through 7 (of 7 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

osama.g.y.a (12/6/2009)
We have a table in the SQL database name Alies This table note of each period of the disappearance of all its contents
We do not know what is the reason??!
For your information the table is not deleted, but I think it is the work of Truncate Table

Understood.

As is well known that this is his job to delete records in the table

Whose Job ?

Is there a way to protect this table and monitor changes that occur upon ???!!

If you have lot you users who have delete permissions, try to run a profiler trace to find who did it.

And prevent any person from the work to Truncate this table
Could you please help me writing the command for restricting the table to be truncated for all users

Deny DELETE is what is on top of my mind, not done this to all users, only did for particular users

using

USE [DATABASENAME]

GO

DENY DELETE ON [dbo].[TableName] TO [User123]

GO

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

A backdoor method to prevent any user from using the the TRUNCATE TABLE command would be to create an INDEXED VIEW on this table, as a table contained in an indexed view can not be truncated. Of course this indexed view would serve no other purpose, until you corrected the actions of the individual(s) who are using the command, and yet allow users to delete individual rows.

Refer to Books On Line subject:

TRUNCATE TABLE (Transact-SQL)

Restrictions
You cannot use TRUNCATE TABLE on tables that:
Are referenced by a FOREIGN KEY constraint.
Participate in an indexed view.

Then when some user complains you will have identified the individual responsible.

Admittedly not a direct method, rather a sort of a bandage to prevent what you desire to prevent, unless some else assists you with a better idea.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

osama.g.y.a SSC Rookie Points: 34 More actions · Answer 3

Thank you all very much for help

Briefly the problem that the data in a table alias that someone deleted by using a command Truncate

All I want to block the use this command or knowledge of this work is

Has been the experience of profiler

And has not been defined, use the Truncate

***Truncate Table will not fire a DDL trigger.

=====================================

Restrictions

You cannot use TRUNCATE TABLE on tables that:

Are referenced by a FOREIGN KEY constraint.

Participate in an indexed view.

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\Can you help me in learning how to work FOREIGN KEY for the table alias

I thank you for helping me

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Of the two suggestions, the one I would prefer is:

Participate in an indexed view.

As long at the view exists, it will block the truncate command. The view does not have to be utilized anywhere, it must simply exist and use data from the table you wish to keep from responding to the Truncate command.

All you would have to do is write down or remember what you have done when you truly want to truncate the particular table. Now remember this will NOT stop anyone from executing the T-SQL command DELETE * FROM your table name without a WHERE clause.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Truncate Table requires very high permissions, just delete rights on the table is not enough. Check what rights are required (books online) and then make sure that no one has them except the DBA.

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

osama.g.y.a SSC Rookie Points: 34 More actions · Answer 6

osama.g.y.a

SSC Rookie

Points: 34

January 2, 2010 at 11:52 pm

#1098218

Thank you very much for the assistance