7

A user (Windows login) just complained he was denied the execution of a procedure. I went to check and verified he had the privileges to execute it. I didn't change anything (and right now I'm the only one with admin privileges do to so if needed) and after two unsuccessful attempts he tried to run the SP for the third time and it worked.

I have XE configured to catch error messages and it captured twice the error code 229:

The EXECUTE permission was denied on the object 'storedProcedureName', database 'databaseName', schema 'schemaName'.

Is there any situation where this behavior is expected?


Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64)

0
19

If the Windows user account was added to an Active Directory (AD) group in the meantime, and that AD group had permission to run the procedure, then that could create this scenario where a user gained access to a procedure with no changes within SQL Server.

Also get the network admin to check if the user logged off and on again. Group membership only gets assigned at logon so if the user was added to a group and had not logged off he would not have had that group membership in his token. - Spörri

Related Q&A: How do I assign an entire Active Directory group security access in SQL Server 2008?

1
  • Josh, unfortunately I couldn't verify the audit yet, but we've been having some network related problems in the past couple of days and most of the answers for the OP are pointing to something related to the AD, so I'm assuming this is just another knee jerk of a domain problem and I decided to wait for them to fix the DC problem first to avoid flooding the network team with reflexes of the real problem. As soon as I have an answer I'll gladly share the outcome of the OP troubleshooting =D
    – Ronaldo
    Nov 11 '20 at 23:35
14

Another possible root cause would be a misunderstanding of how a stored procedure definition is terminated. Take this for example:

CREATE OR ALTER PROCEDURE dbo.MyTestProc
AS
BEGIN
    SELECT result = 1;
END
GRANT EXECUTE ON dbo.MyTestProc TO [SomeUser];

The intention here is to define the procedure and then grant permissions to it.

However, the first time that batch is executed, permissions on dbo.MyTestProc will not change. The first time the stored proc itself is executed, via EXEC dbo.MyTestProc, the permissions will be granted to allow [SomeUser] to execute the proc.

The corrected batch to create that proc and grant permissions to it would be:

CREATE OR ALTER PROCEDURE dbo.MyTestProc
AS
BEGIN
    SELECT result = 1;
END
GO
GRANT EXECUTE ON dbo.MyTestProc TO [SomeUser];

The GO between the procedure definition, and the GRANT statement means that when you execute that batch, the procedure will be created and the grant will immediately take effect.

If the proc was defined incorrectly, as shown above, the user may not have been able to execute it. If you then executed the proc as a sysadmin, the first execution of it would have granted them access.

1
  • 2
    Thanks, Max Vernon. Really nice to know it, but that was easy to verify that was not the case here for I tested creating a procedure like like the one you mentioned - the grant was kept on the procedure definition. However, I generated the scripts of the procedure from the error and there's no grant on the DDL. Nonetheless, it certainly is another situation where the OP behavior is expected.
    – Ronaldo
    Nov 10 '20 at 16:21
11

Perhaps the user had an invalid Kerberos ticket (for example expired), which caused the two "access denied" errors. At some point in time after that the user's workstation contacted a DC and renewed the ticket, allowing the user to execute the procedure the third time.

2
  • 4
    That's a very interesting possibility; I have noticed that since I've been working from home and logging in to the company network by VPN I sometimes get "Access Denied" errors in SQL Server Management Studio the first couple of times I try to access a SQL Server (this has happened on more than one server, on more than one occasion), but that on the third or fourth attempt the logon is successful. Now I think about it, this has only been happening since I've been putting my laptop to sleep in the evening and reconnecting to the timed-out VPN session in the morning. Something to look into.
    – Spratty
    Nov 11 '20 at 12:12
  • 2
    You might be able to approximate this issue using the command-line utility klist.exe with the purge option, which will delete all kerberos tickets stored in your current session. You'd need to exit SSMS first, then run klist purge from the command line, then immediately try connecting with ssms. Purging the tickets via klist means your workstation session must contact a domain controller to obtain new kerberos tickets. If the DC takes a while to respond, that might hypothetically result in an access denied error.
    – Hannah Vernon
    Nov 11 '20 at 18:52
8

Related to Josh's answer: An alternative could be that this person was a member of an AD group for which there was an explicit DENY to do the operation. And then this person was removed from the AD group with that DENY.

I'm talking about a user in the database. A user that points to a login, where that login points to an AD group i.e. the deny could have been done to as user, in the end pointing to that AD group. Your person might have been member of that AD group. Pretty much the same as Josh's example, but in reverse.

Not as likely as Josh's example, but plausible.

1
  • 1
    I think now I see. You're assuming one of the other users on the database is mapped to a windows group instead of a windows login that is member of a group. Tricky. But I don't have any database user mapped directly to a windows group (acting as SQL Server login), I only have database users mapped to windows logins that are members of a windows group added as an instance login. Yet, like the other answers, not my case, but a possible scenario.
    – Ronaldo
    Nov 10 '20 at 17:34

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.