Check whether GRANT EXECUTE TO user or role was applied

Question

In Microsoft SQL Server, I can use

GRANT EXECUTE TO <principal>

to grant execute permission to some user or role. I'm interested in detection:

How can I equally simply check whether that GRANT EXECUTE command was already applied to given user/role? (by me or by some other administrator)

Example:

If I use GRANT EXECUTE TO user01 and return back after few weeks: is there a simple way to check whether I (or someone else) already used GRANT EXECUTE TO user01?

miroxlav · Accepted Answer · 2015-10-02T16:00:53.317

I found where it is stored, so until someone posts simpler answer, I'll keep this code snippet at hand for case of checking:

DECLARE @username nvarchar(128) = 'user01';

SELECT COUNT(*) FROM sys.database_permissions 
    WHERE grantee_principal_id = (SELECT UID FROM sysusers WHERE name = @username) 
        AND class_desc = 'DATABASE'
        AND type='EX' 
        AND permission_name='EXECUTE' 
        AND state = 'G';

Result 0 means negative answer, 1 means positive.

Check whether GRANT EXECUTE TO user or role was applied

1 Answers1