Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

1 sp out of 4 not executing even though user has execute permissions on all 4 Expand / Collapse
Author
Message
Posted Monday, January 07, 2013 2:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 10:42 AM
Points: 20, Visits: 81
I 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 2
The 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 @Mysql
Post #1403871
Posted Monday, January 07, 2013 2:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 10:42 AM
Points: 20, Visits: 81
I replied once to this - and it didn't 'take', so trying again, but shorter reply:

I tried using execute as login instead and am now getting:

Msg 229, Level 14, State 5, Line 2
The 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.
Post #1403875
Posted Monday, January 07, 2013 9:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 20,460, Visits: 14,086
If 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.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1403960
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse