OpenRowSet Fails


  • ss2008 R2

    I can execute a simple query with my openrowset, and even execute a stored proc which executes a simple query which returns a result set.  But if the proc inserts rows into a table datatype, for example,  the openrowset fails with a permissions error message.  I can connect in Studio and rExec the same stored proc.   But there appears to be some permission issue when the OpenRowSet is used.  I am surprised that an Openrowset has a permission issue given that the login I am using is a sysadmin.  I know there are some registry settings and ad hoc distributed queries has been set to 1. I notice a lot of people fight this, so I suspect the answers should be known.

    Any suggestions would be appreciated.

    Wednesday, July 09, 2014 1:07 AM