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