What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change?

Database Administrators Asked by Ronaldo on December 16, 2020

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)

4 Answers

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.

Answered by Greenstone Walker on December 16, 2020

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

    SELECT result = 1;
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:

    SELECT result = 1;
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.

Answered by Max Vernon on December 16, 2020

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.

Answered by Tibor Karaszi on December 16, 2020

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?

Answered by Josh Darnell on December 16, 2020

Add your own answers!

Related Questions

Dbca hangs at 59%

0  Asked on December 6, 2020 by user133197


Performance issue in Postgresql 12

0  Asked on December 3, 2020 by raghu-mutyam


Linked Server throws metadata error

1  Asked on November 30, 2020 by cam


What is an ODBK file and how to restore that?

1  Asked on November 14, 2020 by hasnain


Calculating percentage over sub query SQL

1  Asked on October 29, 2020 by patrick-kusebauch


How to select the rows affected by an update

2  Asked on October 27, 2020 by enharmonic


Corruption in mariadb binlog

2  Asked on October 18, 2020 by ctutte


“SQL command not properly ended” with trigger

1  Asked on September 28, 2020 by exilevoid


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP