I just spent hours trying to figure out how an app had privilege to execute stored procedures.
TIL from StackExchange: Turns out the
ON clause is optional for
GRANT, allowing a role to be granted execute on everything. I had to laugh at the very bottom of this MSDN page after I had tried my best to validate the syntax.
CREATE ROLE Test GRANT EXECUTE TO Test
Ok, so that's cool and all (not very principle-of-least-privilege-esque).
However: How am I to discover or detect that grant?
I tried looking through all the SQL Server Management Studio login, role, user, and schema privilege GUIs and don't see this type of Grant identified anywhere.
While debugging, I found questions about enumerating privileges in a query and while those are related, this scenario is neither mentioned nor covered by the answers there:
- How do I determine using TSQL what roles are granted execute permissions on a specific stored procedure?
- List all permissions for a given role?
- can exec permissions be granted to a role in SQL Server 2008 / 2012
I'm looking for the most reliable way of identifying this method of granting (or denying) privileges.