0

I was trying to truncate a table as the following:

TRUNCATE TABLE [servername].[dbname].[schema].[table]

I got error:

object doesn't exist or you don't have permissions.

However, if I remove the servername part, it works.

Can anyone explain why I cannot use four part name in truncating? Thanks.

Improve this question
6
  • are you trying to use this over a linked server connection or on the local server?
    – RubberChickenLeader
    Commented Mar 14, 2014 at 19:35
  • nope I do it in the same server.
    – Joann.B
    Commented Mar 14, 2014 at 19:36
  • 1
    If it's on the same server, then why do you need the four part name? According to MSDN, it only accepts up to three part naming (database.schema.table).link
    – GoodwinSQL
    Commented Mar 14, 2014 at 19:54
  • @SQL_JGood so how can you explain that you can query the data using this way , but you cannot truncate a table using it?
    – Up_One
    Commented Mar 14, 2014 at 19:56
  • This is because we are actually put a synonym after truncate table, and the synonym contains the server name. @SQL_JGood
    – Joann.B
    Commented Mar 14, 2014 at 19:57

1 Answer 1

Reset to default
7

It's because TRUNCATE is DDL rather than DML

From MSDN:

TRUNCATE TABLE 
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
[ ; ]

Also, regarding permissions:

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.

Notice the db_ddladmin requirement. As DDL, the four-part naming isn't allowed. Compare it to Technet info on the FROM clause:

If the table or view exists in another database on the same instance of SQL Server, use a fully qualified name in the form database.schema.object_name.

If the table or view exists outside the instance of SQL Server, use a four-part name in the form linked_server.catalog.schema.object. For more information, see sp_addlinkedserver (Transact-SQL). A four-part name that is constructed by using the OPENDATASOURCE function as the server part of the name can also be used to specify the remote table source. When OPENDATASOURCE is specified, database_name and schema_name may not apply to all data sources and is subject to the capabilities of the OLE DB provider that accesses the remote object.

Sources:

http://msdn.microsoft.com/en-us/library/ms177570.aspx

http://technet.microsoft.com/en-us/library/ms177634.aspx

Edit: To answer your question on how to do it, write a stored procedure in the target database. Grant access to execute the procedure, and call truncate from the procedure.

Improve this answer
2
  • Thank you David. But how can we realize truncating a table from a linked server? I found some solution like "EXECUTE [server].master.dbo.sp_executesql N'SQL YOU WANT TO RUN'". But this means the user should have execute permission on master db, isn't it?
    – Joann.B
    Commented Mar 14, 2014 at 20:04
  • 1
    You can have a stored procedure in your linked procedure that calls TRUNCATE TABLE. Then grant access to the procedure to your user account.
    – David Crowell
    Commented Mar 14, 2014 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.