I'm aware this is an old post, But I feel there may be a small error in the schema script section.
that section will result in an out put something like this
GRANT SELECT TO SCHEMA::[HumanResources] (The syntax may be wrong, more over it fetches the first schema and in the subsequent order- and not the reuired schema)
The script may have to modified this way
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_id
WHERE class = 3
Now the result returns as (For e.g)
GRANT SELECT ON SCHEMA::[Sales] TO [ag]
By the way thank you for consolidating this awesome script 🙂
-Arun