deleting the data

Question

deleting the data

sandhyarao49

SSCrazy

Points: 2215
More actions
September 20, 2008 at 7:56 am

#94133

HI To all,
My requirement is ....................
Users have the ddl rights on the database.
I don't want the user's to delete the data from a particular table.(only one table).
Could any one help me in writing the script for the same.
Thanks,
Sandhya

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

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

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

DDL rights means that the users will be able to truncate the table, not delete from it (unless they also have delete rights from elsewhere)

Do they really need ddl rights?

If they have delete rights from elsewhere, deny them delete on the table. That will prevent them from doing a delete from, but not a truncate.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

You can apply INSTEAD OF trigger FOR DELETE on that particular table

if you want i can give you script also but if you try yourself 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

bhuvnesh.dogra (9/23/2008)
You can apply INSTEAD OF trigger FOR DELETE on that particular table

Won't help against truncates. Truncate doesn't fire triggers.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

i know .....truncate cant be stopped by trigger 🙂 but

can we think beyond the available features of sql 2005

is there any sys tables or catalog view by which can figure out that truncate coomand has been fired ?

then we might also stop truncate ??? i know i m talking base less but just think it over

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

bhuvnesh.dogra (9/23/2008)
i know .....truncate cant be stopped by trigger 🙂 but
can we think beyond the available features of sql 2005

As a purely theoretical exercise?

is there any sys tables or catalog view by which can figure out that truncate coomand has been fired ?

Not that I'm aware of. It doesn't appear in the plan cache (because it's a DDL statement) and there's no table that stores a comprehensive list of all commands executed.

It would appear in a profile trace if you had one running. It might (not sure) appear in the default trace.

The 2008 Change Data Capture might pick it up, because that reads the tran log for changes. Haven't tested it.

then we might also stop truncate ??? i know i m talking base less but just think it over

How would you stop a truncate? It doesn't even fire DDL triggers.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

yeah ...u r right 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)