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 1 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 10:39 AM

Grasshopper

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

SQLRNNR

Posted Monday, January 07, 2013 10:59 AM

SSCoach

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

Error messages for the ones not working?

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 #1403759

Jamble

Posted Monday, January 07, 2013 11:04 AM

Grasshopper

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

SQLRNNR

Posted Monday, January 07, 2013 11:06 AM

SSCoach

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

Is the table in the same database or a different database?

Post #1403763

Jamble

Posted Monday, January 07, 2013 11:14 AM

Grasshopper

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

SQLRNNR

Posted Monday, January 07, 2013 11:22 AM

SSCoach

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

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?

Post #1403772

Jamble

Posted Monday, January 07, 2013 11:31 AM

Grasshopper

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

SQLRNNR

Posted Monday, January 07, 2013 11:35 AM

SSCoach

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

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?

Post #1403781

Jamble

Posted Monday, January 07, 2013 12:32 PM

Grasshopper

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

lptech

Posted Monday, January 07, 2013 1:42 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 96, Visits: 984

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions