I created a stored procedure on a MSSQL server from my local machine via SSMS. I see this SP in the object explorer when I expand the database 'Stored Procedures' node. I am able to execute the SP in the normal manner.
However, if I RDP to the SQL Server, load SSMS on the server, and expand the 'Stored Procedures', I don't see the SP. In fact, there are several stored procedures I don't see. If I try to execute the stored procedure, I get an error message:
Msg 229, Level 14, State 5, Procedure SayHi, Line 1 The EXECUTE permission was denied on the object 'SayHi', database 'master', schema 'dbo'.
I have verified:
- I am connecting to the correct server via RDP
- I am connecting to the same server in both SSMS sessions
- I am viewing the same database (master) in both SSMS sessions
- I am logging in as the same user in both SSMS sessions
- This is a procedure that I just created. Even when executing on the server, the server knows the procedure exists - otherwise the error message would say "Could not find stored procedure". That's why I'm confident I'm communicating with the correct server / database. The other SP's that I'm unable to see have been around for a long time - some I created, others were already there.
- I have 'Refresh'ed the tree (irrelevant to the EXECUTE denied response)
- The SP is in the master database, if that makes any difference
- My local SSMS is SSMS 2017, the server is SQL 2012 SP3
- My colleague sees all of the SPs regardless of whether he is using SSMS on his own machine or while RDP into the server.
- I have tried logging out of the server and restarting SSMS.
Any ideas why I can't see / execute the SP while logged in at the server, despite being logged in as the same user??