SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


1 sp out of 4 not executing even though user has execute permissions on all 4


1 sp out of 4 not executing even though user has execute permissions on all 4

Author
Message
Jamble
Jamble
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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
Jamble
Jamble
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66963 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search