What is the minimum level of permission that is required to effectively determine if an object is signed using sys.crypt_properties?

Question

I am signing stored procedures using certificates.

Inside the stored procedure what is the minimum requirement needed to determine whether the the running module is signed.

If not signed, I would like to quickly show an error message and exit the module or SP.

The same question asked a different way:-

execute as user='lowprivilege'

select *

from sys.crypt_properties

revert;

will return an empty record set for a low privilege user;

Answer 1

The topic for sys.crypt_properties says:

Permissions The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.

From this we may conclude that if the user has right to run a procedure, it also has rights to see the contents in sys.crypt_properties. However, this script shows that this is not the case:

CREATE PROCEDURE my_signed_sp AS
   SELECT * FROM sys.crypt_properties WHERE major_id = @@procid
   SELECT COUNT(*) FROM sys.query_store_query
   SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')
go
CREATE USER nisse WITHOUT LOGIN
GRANT EXECUTE ON my_signed_sp TO nisse
-- GRANT VIEW DEFINITION ON my_signed_sp TO nisse
go
EXECUTE AS USER = 'nisse'
go
EXEC my_signed_sp  -- Fails with permission error.
go
REVERT
go
CREATE CERTIFICATE MyCert 
   ENCRYPTION BY PASSWORD = 'TemporaryInTipperary'
   WITH SUBJECT = 'Just testing'
go
ADD SIGNATURE TO my_signed_sp BY CERTIFICATE MyCert WITH PASSWORD = 'TemporaryInTipperary'
go
CREATE USER MyCertUser FROM CERTIFICATE MyCert
GRANT VIEW DATABASE STATE TO MyCertUser 
go
EXECUTE AS USER = 'nisse'
go
EXEC my_signed_sp  -- Succeeds.
go
REVERT
go
DROP USER nisse
DROP USER MyCertUser
DROP PROCEDURE my_signed_sp
DROP CERTIFICATE MyCert

However, the result set from sys.crypt_properties is empty in both cases.

The script also indicates two solutions:

1. If you uncomment the line with GRANT VIEW DEFINITION, the user sees the row for my_signed_sp.
2. Rather than peeking sys.crypt_properties, which obviously is not reliable, use fn_my_permissions to check for the permission you want to have bundled with the procedure. You can see that when the procedure is signed, the permission VIEW DATABASE STATE is returned, even though it has not been granted to the user nisse.