InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 10:02:09 GMT20http://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxIf you stub a proc with the same permissions, do you get results from the openquery piece of the query? Eliminate the Join and the insert - make sure results are being passed back from the openquery linkedserver.Mon, 07 Jan 2013 21:54:24 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxI replied once to this - and it didn't 'take', so trying again, but shorter reply:I tried using [u]execute as login [/u]instead and am now getting:Msg 229, Level 14, State 5, Line 2The SELECT permission was denied on the object 'Program', database 'MyDB', schema 'dbo'.the Program table (sql server) inner joins with the results returned via the OPENQUERY against the linked server.I can run the sp myself otherwise.Mon, 07 Jan 2013 14:14:13 GMTJamblehttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxI ran it as the other user. I ran it as myself (both times using execute as user). Got the same error message. Running it WITHOUT execute as user, it runs fine for me.In the meantime I just tried using 'execute as login'. I seem to be getting somewhere using that, as I am now getting:'Msg 229, Level 14, State 5, Line 2The SELECT permission was denied on the object 'Program', database 'mydbname', schema 'dbo'.However, users have no permissions against any tables - only execute permissions via the role. I get data from linked server using OPENQUERY. The returned data links to local SQL Server table & is then inserted into SQL Server table. (@sqlqry has been put together in stmts prior to this running):SET @Mysql = N' INSERT INTO MySQLServerdb.dbo.Mytable ( columns )SELECT listed columns FROM OPENQUERY(linked_server_Name,' + @sqlqry + ') AS s INNER JOIN Program AS p ON s.program_id = p.ProgramID WHERE s.Col_ID NOT IN (SELECT ColID FROM MyTable)'exec @MysqlMon, 07 Jan 2013 14:04:10 GMTJamblehttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxHave you tried running the linked server query from SSMS both as yourself and with the EXECUTE AS USER = ? It could be a permissions issue with the other DBMS that is sending back a return code that SQL Server is mis-interpreting.Mon, 07 Jan 2013 13:42:04 GMTlptechhttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxThe linked server has been set up to use a generic login rather than the login used by sql server. However, when I tried to run the sp as another user, ([u]including myself[/u]!) I get the following error: (so not sure why that is happening - if I run it without use of the 'EXECUTE AS user =' line, it runs fine)Msg 15274, Level 16, State 1, Line 2Access to the remote server is denied because the current security context is not trusted.Mon, 07 Jan 2013 12:32:27 GMTJamblehttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxAnd the linked table, does this role have the appropriate permissions on it?Have you tried to run the proc while logged in as a user in that role (not yourself) from within ssms?Mon, 07 Jan 2013 11:35:34 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxAll users have connect permissions onlyRole has execute permission - and user is part of role - this is how they are granted execute permissions -via this roleRole has deny execute on 1 sp only - which isn't used in this scenariothere are no permissions against tables directlyMon, 07 Jan 2013 11:31:51 GMTJamblehttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxDoes the table involved in the insert, or any of the tables in the non-functioning sp, have deny permissions for the users or role?Mon, 07 Jan 2013 11:22:58 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxThe sp is querying a linked table (non-sql db) to get the records to add to the SQL Server table. The other 3 queries ARE working which have the same setup (different tables).Also... the same login (a generic one we've been given) is used to access the non-sql db. So, same login used against that one irregardless of whoever is running the spMon, 07 Jan 2013 11:14:19 GMTJamblehttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxIs the table in the same database or a different database?Mon, 07 Jan 2013 11:06:15 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxunfortunately not. the SP in question is supposed to add records to a table - and this isn't happening. Only when I run the app.Mon, 07 Jan 2013 11:04:47 GMTJamblehttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxError messages for the ones not working?Mon, 07 Jan 2013 10:59:34 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1403751-146-1.aspxWe have permissions set up so users have permissions only on stored procs. I have a role set up with Execute permission on DB and have added user to the role. The role has a deny on only 1 specific SP (which is NOT being used in this case). The SP will run for me (as sysadmin), (being run via .net pgm) but not for the user. Also, the other 3 SPs will run for the user fine. Any suggestions on how I can troubleshoot this problem from the database side? Thanks for the help!Mon, 07 Jan 2013 10:39:34 GMTJamble