• aruopna (5/14/2012)


    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

    Hi Arun,

    Thanks for your feedback. I suppose a lot of people (including myself) do not use schema-based permissions. Do you have an example that I can establish schema level permissions and test out of the update?

    I ran the following code, but I cannot get it to come back as part of the DB level schema permissions.

    GRANT SELECT ON SCHEMA::[test_perms_schema] TO [test_perms_login]

    Any example you can provide that I can re-test the code would certainly help. I will then get the updated script posted to include your fix. I truly appreciate it, but want to be sure this time around.

    Thanks, and have a great day.

    Steve