September 7, 2017 at 9:36 am
Hello fellow SQL Server pros... I am a bit confused by a behavior that we are seeing both in SQL Server 2012 Enterprise and SQL Server 2016 Enterprise and Developer.
The setup...
We have a procedure that calls a nested procedure. We have a production user for accessing our database that had EXECUTE privileges on the outer procedure, but did not have any privileges on the nested sub-procedure. We were getting errant results in the data returned in our production environment and found this issue.
The confusion...
What I am failing to understand is how does the outer procedure not produce an error on execution when it tries to execute the nested sub-procedure? If we try to execute the sub-procedure using the production logon, we get an error. ...and the primary procedure is clearly not getting any results from the sub-procedure, so it isn't getting executed at run time.
Can anyone explain why this is happening and what the rationale behind the decision to implement this behavior is?
Has anyone else experienced this?
Is there any way to reliably check these dependencies? (the nested calls are inside of TRY...CATCH wrappers and I think that is hiding them from the standard check dependencies routine)
PS... we are also experiencing similar behavior when a procedure calls a view and the user has privileges on the procedure but none on the view.
Any help is greatly appreciated.
Scott B Dragoo
Enterprise Architect
Vitality Group
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
September 7, 2017 at 10:05 am
The inner procedure is running under the ownership of the outer procedure. It's similar to how you can give someone permission to a procedure but not to the underlying tables. As long as the schema defined as the owner of the procedure has valid access to the underlying tables, then the person who has access to the procedure gets access to the underlying tables, but only through the procedure. Same thing here. They have access to that other procedure, but only while calling it in a nested fashion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2017 at 10:52 am
This behaviour is down to which is known as 'ownership chaining'. You may find this link informative.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 7, 2017 at 11:19 am
OK. That answers what happens when the nested proc exists but is missing permissions, sort of... The nested / inner procedure is not returning any data. If we assign execute privileges to the inner one, then it behaves correctly and returns data as expected.
We also see this odd behavior in cases where the inner / nested procedure does not exist at all. (I think that in these cases, the inner procs existed at one time but are not there now)
Additional note - these executions occur as part of SSIS packages and most of the outer procedures have TRY...CATCH wrappers in them with RAISEEERROR statements.
Again, Thank you everyone for the help on this one.
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
September 7, 2017 at 1:16 pm
s b dragoo - Thursday, September 7, 2017 11:19 AMOK. That answers what happens when the nested proc exists but is missing permissions, sort of... The nested / inner procedure is not returning any data. If we assign execute privileges to the inner one, then it behaves correctly and returns data as expected.We also see this odd behavior in cases where the inner / nested procedure does not exist at all. (I think that in these cases, the inner procs existed at one time but are not there now)
Additional note - these executions occur as part of SSIS packages and most of the outer procedures have TRY...CATCH wrappers in them with RAISEEERROR statements.
Again, Thank you everyone for the help on this one.
I cannot replicate this. Please see the following code which attempts to do so, yet results in a 'Could not find stored procedure ...' error.DROP PROC IF EXISTS #Nonsense;
GO
CREATE PROC #Nonsense
AS
SELECT 1;
GO
EXEC dbo.#Nonsense;
GO
DROP PROC IF EXISTS #MoreNonsense;
GO
CREATE PROC #MoreNonsense
AS
BEGIN TRY
EXEC dbo.#Nonsense;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
GO
EXEC dbo.#MoreNonsense;
GO
DROP PROC IF EXISTS #Nonsense;
GO
EXEC dbo.#MoreNonsense;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply