Signo.marcoo - Monday, September 25, 2017 10:43 AM
Some of those scripts you have found have options to just script out the revoke statements. You can also modify them to change from revoke to grant. The script in this blog has one with an option to script - you could modify that to do grants as well:
Remove Public and Guest Permissions
If you read that link, it also explains some of the issues that could come up - especially with connect to.
A lot of the scripts you can find won't necessarily work exactly how you need them to so it's good to have a solid understanding of those so you can make the modifications you need. Many queries you can modify to generate the sql statements to be run.
For changing permissions, you can first modify to revoke from public and then use the same script to modify for grant to the new role. You would want public to still have the database permissions when generating both the revoke from public and grant to new role. So if you were to do something like:SELECT 'REVOKE ' + p.permission_name COLLATE DATABASE_DEFAULT
+ ' ON ' + schema_name(o.schema_id) + '.' + o.name + ' TO public;'
FROM sys.all_objects o
INNER JOIN sys.database_permissions p ON o.object_id = p.major_id
INNER JOIN sys.database_principals u ON u.principal_ID = p.grantee_principal_id
WHERE u.name = 'public'
You can then just change the REVOKE to grant and the principal to your other role. So if I just changed them in the above, the first two lines would be:
SELECT 'GRANT ' + p.permission_name COLLATE DATABASE_DEFAULT
+ ' ON ' + schema_name(o.schema_id) + '.' + o.name + ' TO NewRole;'....<and the rest of the query>
You can pretty easily do that with the script in the link I posted above. And select the option to generate the script.
Sue