SQL Server query to get all stored procedures, tables and views execute permissions

To list all Stored Procedures in your database that an account has execute permissions on, simply update the “Execute as user” statement below..

EXECUTE AS user = 'myUsername'
SELECT SUSER_NAME(), USER_NAME();
select name,
    has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute
from sys.procedures
revert;

To list all permissions applied explicitly to tables and views for a username, use the following query…

EXECUTE AS USER = N'myUserName';
 GO
 SELECT
    s.name,
    o.name,
    p.[permission_name]
 FROM sys.objects AS o
 INNER JOIN sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name)
   + N'.' + QUOTENAME(o.name), N'OBJECT') AS p
   WHERE o.[type] IN (N'U', N'V') -- tables and views
   AND p.subentity_name = N''; -- ignore column permissions
 GO
 REVERT;