Solomon Rutzky shows how you can use certificate-based module signing to grant users fine-grained permissions:
-- 1) Create the Certificate: CREATE CERTIFICATE [Permission$ViewServerState] ENCRYPTION BY PASSWORD = 'UseBetterPassword!' WITH SUBJECT = 'VIEW SERVER STATE permission', EXPIRY_DATE = '2099-12-31'; /* The password is used to protect only the Private Key, not the Public Key or the Certificate in general. The default expiration date is 1 year from the date the Certificate is created. However, in many situations, such as with Module Signing, the expiration date is actually ignored. Still, I prefer to set the expiration date to 2099. */
The DBA handling this server in the year 2100 is going to be really upset. Certificate signing works wonders in conjunction with stored procedures, as you can grant procedures all kinds of rights needed for your application but users cannot run ad hoc queries expecting those same rights.