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)
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:
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];
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
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
Answered by Josh Darnell on December 16, 2020
4 Asked on December 6, 2020 by ran
1 Asked on December 5, 2020 by aleksey-vitsko
1 Asked on December 3, 2020 by monya-feldman
1 Asked on December 3, 2020
2 Asked on December 3, 2020 by martyb
0 Asked on December 3, 2020 by raghu-mutyam
1 Asked on November 28, 2020 by spksmithy
1 Asked on November 14, 2020 by hasnain
1 Asked on November 7, 2020 by curiouschad
0 Asked on October 31, 2020 by jim-d
1 Asked on October 29, 2020 by patrick-kusebauch
1 Asked on October 12, 2020 by chris-di-carlo
0 Asked on October 10, 2020 by jonathan-hurdman
1 Asked on October 3, 2020 by deadmann
1 Asked on September 28, 2020 by exilevoid
0 Asked on September 25, 2020 by pgerrits
Get help from others!