SQL Server SSMS – EXECUTE Denied When Connected Locally

sql serverssms

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

Also:

  • 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??

Best Answer

Your login is Windows login, so it can get its permissions from different Windows groups.

You loose some of them when logging locally, this usually happen when you launch SSMS not as administrator: you are still a member of BUILTIN\Administrators but UAC strips out your rights.

You should execute the following code in both cases: when you are logging remotely and locally, and check if in usage you see DENY ONLY when logging locally:

select distinct name, type, usage
from sys.login_token
where principal_id > 0

What to do next depends on what you see as the results. If you see BUILTIN\Administrators DENY ONLY just run your SSMS as administrator. If there is some other difference in the results, please post it here.

Related Question