So today I came across something that I thought seemed like a simple task. A client wanted to run certain commands on a remote server via a linked server. So I created a linked server for him and granted the required read/write permissions to his ID on the remote server.
But he reported that he was not able to run any TRUNCATE commands on the remote server.
Upon further investigation found that you need ALTER rights on the tables that you want to truncate. I assumed that TRUNCATE was a DML statement but that does not seem to be the case. It's considered as a DDL statement and the Login requires ALTER permissions on the table. So I was able to grant that access and his Login could truncate tables when he logged into the server. But when he executed the TRUNCATE commands on the linked server it would not let him do that. The error that he received was as follows.
Error
Msg 4701, Level 16, State 1, Line 1
Cannot find the object "TableXYZ" because it does not exist or you do not
have permissions.
After looking over the net for similar errors I found out that you can not run TRUNCATE statement on a remote server via a linked server. But fortunately someone had figured out a work around and I thought I will share this with you.
You can run a RPC for sp_executesql and run the truncate statement which seems to work. So first I change the settings for the linked server to run remote procedure by settings RPC to TRUE. Then executed the following command.
But he reported that he was not able to run any TRUNCATE commands on the remote server.
Upon further investigation found that you need ALTER rights on the tables that you want to truncate. I assumed that TRUNCATE was a DML statement but that does not seem to be the case. It's considered as a DDL statement and the Login requires ALTER permissions on the table. So I was able to grant that access and his Login could truncate tables when he logged into the server. But when he executed the TRUNCATE commands on the linked server it would not let him do that. The error that he received was as follows.
Error
Msg 4701, Level 16, State 1, Line 1
Cannot find the object "TableXYZ" because it does not exist or you do not
have permissions.
After looking over the net for similar errors I found out that you can not run TRUNCATE statement on a remote server via a linked server. But fortunately someone had figured out a work around and I thought I will share this with you.
You can run a RPC for sp_executesql and run the truncate statement which seems to work. So first I change the settings for the linked server to run remote procedure by settings RPC to TRUE. Then executed the following command.
execute mylinkedserver.dbname.dbo.sp_executesql "TRUNCATE TABLE dbo.TableXYZ"
No comments:
Post a Comment