I have implemented the stored procedure to check if a specific [schema].[procedure]
exists.
CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@procedureName varchar(128)
AS
SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
WHERE s.[name] = @schemaName AND p.[name] = @procedureName), 1, 0)
GO
When I tested with sa
it selected 1 as I expected but when I tried with a minimum permission account which only granted EXECUTE
to the procedure, it selected 0.
My assumption of stored procedure mechanism was role/user only requires EXECUTE
to perform the procedure.
We don't need to concern the permission setting of each DB object like CRUD to table and so on.
But it seems sys tables are exceptions? Or did I miss anything?
Moreover, If I don't want to grant select sys.procedures
/sys.schemas
to the role. I just want my procedure to work as expected, what should I do?
Best Answer
This statement is true when ownership chaining applies. With an unbroken chain, users do not need permissions on indirectly referenced objects.
However ownership chaining does not apply metadata visibility when selecting from the catalog views. Users must be granted some permissions on an object in order for it to be visible when selecting from the catalog views.
Below is a script that shows this behavior for your use case. If you need to return objects users have no permissions to use, you'll need to use
EXECUTE AS
or certificate signing to elevate permissions for users of theBlobCheckExisting.usp_DoesStoredProcedureExist
procedure.