Truncating a table from a Linked Server

Let’s assume you are using SQL Server and you want to truncate a table on a Linked Server but are getting errors about permissions.

Msg 4701, Level 16, State 1, Line 1

Cannot find the object "MyTable" because it does not exist or you do not have permissions.

The first thing to check is that you have the proper permissions. MSDN says

“The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.”

Assuming you do actually have permission the problem is probably in the syntax.

My guess is that you tried the same thing I did to start with, which is the following.

truncate table MyLinkedServer.MyDB.dbo.MyTable

You can use truncate table statement on a Linked Server, but not with that syntax and you have to know a trick. Here is the same statement, but using a different syntax.

EXEC MyLinkedServer.MyDB.sys.sp_executesql N'truncate table dbo.MyTable'

It seems a bit convoluted to me, but it works.

NOTE: If you get a message about … is not configured for RPC then click here to read how to fix that issue.