;WITH    Perms_query ( DBname, DB_ObjectName, Permission_name, state_desc, DB_User, Type_Desc, Table_Name, Proc_name, Login_Name )
          AS ( SELECT   DB_NAME(DB_ID()) AS DBName ,
                        OBJECT_NAME(dp.major_id) AS DB_OBJECTNAME ,
                        dp.permission_name ,
                        dp.state_desc ,
                        d.name AS DB_User ,
                        d.type_desc ,
                        ISNULL(t.name, 'NOT A TABLE') AS Table_name ,
                        ISNULL(P.name, 'NOT A PROC') AS Proc_name ,
                        ISNULL(l.name, 'ORPHANED USER') AS Login_name
               FROM     sys.database_permissions AS dp
                        JOIN sys.database_principals d ON dp.grantee_principal_id = d.principal_id
                        JOIN sys.objects o ON o.object_id = dp.major_id
                        LEFT JOIN sys.tables t ON t.object_id = o.object_id
                        LEFT JOIN SYS.procedures P ON P.object_id = O.object_id
                        LEFT JOIN sys.syslogins l ON l.sid = d.sid
               WHERE    dp.major_id >= 0
                        AND d.type IN ( 'S', 'G', 'U' )
                        AND dp.permission_name <> 'connect'
               UNION
               SELECT   DB_NAME(DB_ID()) AS dbname ,
                        dp.name account_name ,
                        USER_NAME(drm.role_principal_id) AS DB_Role_Member ,
                        ' ' ,
                        ' ' ,
                        ' ' ,
                        ' ' ,
                        ' ' ,
                        ' '
               FROM     sys.database_principals dp
                        LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
               WHERE    dp.sid NOT IN ( 0x01 )
                        AND dp.sid IS NOT NULL
                        AND dp.type NOT IN ( 'c' )
                        AND dp.is_fixed_role <> 1
                        AND dp.name NOT LIKE '##%'
                        AND drm.role_principal_id IS NOT NULL
             )
    SELECT  Dbname ,
            DB_ObjectName AS [DB_Object or AcctName] ,
            Permission_name ,
            CASE WHEN state_desc = '' THEN '--'
                 ELSE state_desc
            END AS state_desc ,
            CASE WHEN DB_User = '' THEN '--'
                 ELSE DB_User
            END AS DB_User ,
            CASE WHEN Type_Desc = '' THEN '--'
                 ELSE Type_Desc
            END AS Type_Desc ,
            CASE WHEN Table_Name = '' THEN '--'
                 WHEN Table_Name = 'NOT A TABLE' THEN '--'
                 ELSE Table_Name
            END AS Table_Name ,
            CASE WHEN Proc_name = '' THEN '--'
                 WHEN Proc_name = 'NOT A PROC' THEN '--'
                 ELSE Proc_name
            END AS Proc_Name ,
            CASE WHEN Login_Name = '' THEN '--'
                 ELSE Login_Name
            END AS Login_Name ,
            CASE WHEN Permission_name = 'Execute'
                 THEN 'Grant execute on [' + Proc_name + '] to [' + DB_User+']'
				 WHEN permission_name = 'select ' AND proc_name = 'NOT A PROC'
				 THEN 'Grant select on [' + DBname+ '] to [' + DB_User+ ']'
                 ELSE '--'
            END AS Exec_stmt
    FROM    Perms_query
    WHERE   state_desc <> 'deny'
            AND DB_User NOT LIKE '##%'
    ORDER BY state_desc 






