subhash.raut-762682 (1/14/2015)
Steve,Script is very good and helps in copying db permissions specifically if you are overwriting db from different environment and with different db permissions.
Script output is very clean.
It fails to script any user defined DB roles and thus any users related to it.
Subhash
Hi subhash.raut-762682,
I figured out what was missing. If you update lines 75 and 158 to have the ('G', 'S', 'U') updated to ('G', 'S', 'U', 'R'), then it should work.
The object-level permissions were working (ie grant select on dbo.table_1 to [your_database_role_name], but the permissions assigned to the database role at the database layer were being missed.
i.e. this permission would not be scripted out previously, but will be scripted out after changing those where clauses on the lines I specified:
GRANT CONNECT TO [your_database_role_name]
Thanks for pointing that out and helping me improve the script.
Cheers,
Steve