That is tricky. You must be dbo to truncate. You should change the trigger
to "delete from" instead unless the amount of rows prohibit that.
The other alternative is put the user in the dbo role, then deny the dbo
privileges you don't want them to have. They will only be left with insert,
update, delete, exec, alter table(by virtue of being the table owner) and
because they are dbo they can still truncate.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Alan Gredell
[mailto:mssqldba-ezmlmshield-x10172398.[Email address protected]
Sent: Friday, May 27, 2005 9:17 AM
To: LazyDBA Discussion
Subject: Help on Truncate Table permission issue
We have a table that we have permission to update; the update triggers a
stored proc that includes a truncate table statement. My users don't belong
to the dbowner or ddladmin roles, and the truncate table is failing with
permission denied. Any ideas? There is no "Grant truncate table" statement
(truncate table permission is not transferrable?).
Thanks...
_________________________________________________________________
On the road to retirement? Check out MSN Life Events for advice on how to
get there! http://lifeevents.msn.com/category.aspx?cid=Retirement
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended solely for the person or entity to which it is addressed. Any
review, retransmission, dissemination, or taking of any action in reliance
upon this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please contact the
sender and delete the material from any computer.
MS Sql Server LazyDBA home page