SQL Server Performance Forum – Threads Archive

TRUNCATE TABLE

Hi, As we know of truncate table syntax. It is removed the all table records except table structure. Is it possible? Can I deny truncate table syntax a table? e.g. Truncate Table employee The employee table should not trucnate. I believe anyone will rectified the small issue. Amit Kumar
Mob.: 9873812005

---

You can’t deny for the users who is dbo or higher level access but you can creating FK will not allow anyone to truncate unless removing FK… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

You can control such granular access using fixed database roles, refer to the roles under BOL. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Hi MohammedU., I tried your solution. But I’m still able to truncate the table. Amit Kumar
Mob.: 9873812005

---

Check the following… use tempdb
go
CREATE TABLE [dbo].[authors] (
[au_id] [int] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[titleauthor] (
[au_id] [int] NOT NULL ,
[title_id] [int] NOT NULL ,
[au_ord] [tinyint] NULL ,
[royaltyper] [int] NULL
) ON [PRIMARY]
GO ALTER TABLE [dbo].[authors] WITH NOCHECK ADD
CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED
(
[au_id]
) ON [PRIMARY]
GO ALTER TABLE [dbo].[titleauthor] WITH NOCHECK ADD
CONSTRAINT [UPKCL_taind] PRIMARY KEY CLUSTERED
(
[au_id],
[title_id]
) ON [PRIMARY]
GO ALTER TABLE [dbo].[titleauthor] ADD
FOREIGN KEY
(
[au_id]
) REFERENCES [dbo].[authors] (
[au_id]
)
GO truncate table authors
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

TRUNCATE TABLE requires the ALTER permission on the table, you have to specify what sort of permission associated for that user.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>