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 7, 2013 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 10:42 AM
Points: 20, Visits: 81
We 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!
Post #1403751
Posted Monday, January 7, 2013 10:59 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 21,733, Visits: 15,424
Error messages for the ones not working?



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 #1403759
Posted Monday, January 7, 2013 11:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 10:42 AM
Points: 20, Visits: 81
unfortunately not. the SP in question is supposed to add records to a table - and this isn't happening. Only when I run the app.
Post #1403762
Posted Monday, January 7, 2013 11:06 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 21,733, Visits: 15,424
Is the table in the same database or a different database?



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 #1403763
Posted Monday, January 7, 2013 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 10:42 AM
Points: 20, Visits: 81
The 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 sp
Post #1403766
Posted Monday, January 7, 2013 11:22 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 21,733, Visits: 15,424
Does the table involved in the insert, or any of the tables in the non-functioning sp, have deny permissions for the users or role?



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 #1403772
Posted Monday, January 7, 2013 11:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 10:42 AM
Points: 20, Visits: 81
All users have connect permissions only
Role has execute permission - and user is part of role - this is how they are granted execute permissions -via this role
Role has deny execute on 1 sp only - which isn't used in this scenario
there are no permissions against tables directly

Post #1403776
Posted Monday, January 7, 2013 11:35 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 21,733, Visits: 15,424
And 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?




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 #1403781
Posted Monday, January 7, 2013 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 10:42 AM
Points: 20, Visits: 81
The 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, (including myself!) 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 2
Access to the remote server is denied because the current security context is not trusted.
Post #1403813
Posted Monday, January 7, 2013 1:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:14 AM
Points: 223, Visits: 2,123
Have 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.
Post #1403860
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse