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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » 1 sp out of 4 not executing even though user...

13 posts, Page 2 of 2

««1 2

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

Rate Topic

Display Mode

Topic Options

Author

Message

Jamble

Posted Monday, January 07, 2013 2:04 PM

Grasshopper

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

Jamble

Posted Monday, January 07, 2013 2:14 PM

Grasshopper

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

SQLRNNR

Posted Monday, January 07, 2013 9:54 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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 »

13 posts, Page 2 of 2

««1 2

Permissions