March 26, 2019 at 6:46 am
I have two SQL 2008 R2 servers that have two databases each. I have the exact same stored procedure in one of the two databases on each server. I've granted VIEW DEFINITION and EXECUTE permission on the stored procedure to Public, and have no trouble executing the stored procedure on one of the databases as a particular database user., and we have the exact same user on the other database, but I continue to see the following error when I execute as that user:
USE ABC;
GO
EXECUTE AS USER = 'SSRSSubscription';
EXECUTE dbo.uspGetData;
REVERT;
Which produces the following errors:Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 15151, Level 16, State 1, Procedure uspGetData, Line 116 [Batch Start Line 2]
Cannot find the object 'uspGetData', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Procedure uspGetData, Line 117 [Batch Start Line 2]
Cannot find the object 'uspGetData', because it does not exist or you do not have permission.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 26, 2019 at 8:52 am
I'm an idiot. I just now realized that the sproc in question did not have a GO statement to end the procedure and thus the GRANT statements that were part of the script were actually part of the procedure, and no wonder. The moment I put a GO at the actual end of the procedure, my problem disappeared. Man, talk about a weird way to find out you messed up.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply