I am a bit confused by BOL. I have a second SQL server for another team in our company and they want to be able to update a field in one of our tables when one of their records is updated.
I have a stored procedure on my server that executes with very minimal permissions and will accomplish what they are wanting to do.
We have linked servers setup (and prefer not to do RPC) and I want them to only be able to call only that stored procedure. (As you can tell I don't trust them very much--long story here...). BOL is a bit confusing on the subject but can you call a stored procedure on one machine from another when they are linked together? If so, what is the SQL syntax? Would this work:
Select * from OPENQUERY (LnkSvrName 'EXEC svname.dbname.dbo.usp_storedproc @ID, @int')