1

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

  • Are you sure you're connected as the same account from both locations? Run select SUSER_NAME() from both your local SSMS instance and via the RDP session and if they don't match that's the reason. – John Eisbrener Oct 24 '17 at 18:58
  • Since your problem appears to be 'local', you might want to check to make sure some 'local security policy' isn't reducing your authority on that remote machine. – Scott Hodgin Oct 24 '17 at 19:07
  • @JohnEisbrener yes - SELECT SUSER_NAME() on both my local connection and the server produce the same result - my Windows login, as does the "Login name" property of the connection properties window – Michael Bray Oct 24 '17 at 19:18
  • 1
    Is there a difference in logon tokens when you login locally vs remotely: sys.login_token – Sean Gallardy Oct 24 '17 at 20:18
  • 1
    Perhaps UAC. If you're connecting as a member of BUILTIN\Administrators, that group membership would not be available locally unless you run SSMS with elevation. See msdn.microsoft.com/en-us/library/bb326612.aspx – David Browne - Microsoft Oct 24 '17 at 20:54
0

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.

  • OK so this might be a bit tricky, because my Infrastructure administrator pointed out that I wasn't part of sysadmin group (I thought I was, otherwise didn't expect to be able to create SP in master) but once he added it, it resolved my issue - I can now execute and create SP in master. He also says some perms look messed up - maybe glitch? However, here's the output as it stands at this moment from my local machine, and from the server, respectively: imgur.com/a/84fzZ (the pink is just my domain name, same domain for all lines). I'm not sure how to interpret this - help? – Michael Bray Oct 25 '17 at 13:06
  • It's what I've said: your account was not a member of sysadmin server role, BUT it is a member of BUILTIN\Administrators, and BUILTIN\Administrators IS a member of sysadmin role. So when you connect remotely you have BUILTIN\Administrators token and you inherit its permissions, and you see GRANT OR DENY next to this Win group and sysadmin as a consequence. When connected locally you are still a member of this group BUT you DID NOT RUN SSMS AS ADMINISTRATOR, so even if you are local admin, UAC stripped your permissions, you was executing with lower permissions, you see DENY ONLY in 1st and 6nd – sepupic Oct 26 '17 at 8:19
  • So you see DENY ONLY in 1st and 6nd rows of sys.login_token result. BUT now your account is explicitly added to sysadmin role, and you can see it in 7th (last) row of your result as GRANT OR DENY – sepupic Oct 26 '17 at 8:21
  • Answer accepted. One thing I still don't understand (although this has nothing to do with my original question) is why UAC would treat me differently when logged in locally vs logged in remotely. I have only a naive understanding of UAC in the first place, so I'm sure there's a good reason, but nothing is coming to mind. Could you elaborate on that aspect? Much appreciated! – Michael Bray Oct 26 '17 at 13:03
  • You are just using different PC :) When connect remotely, YOUR SSMS is on pc that has UAC disabled. While it's enabled on the server – sepupic Oct 26 '17 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.