• Signo.marcoo - Monday, September 25, 2017 10:43 AM

    Hi all, I am a sql newbie so, I am sorry if my question it's strange and sorry for my English.
    My company asked to me to remove all permission to the public role.
    I have found some articles and suggestions about that but I have a problems about public permission on the master database.
    I have solved every problems caused by this action on sql instance and sql db but I am not able to understand how this role is necessary on master db.
    I need to replace master public permission to another custom role, I have found some script to display all permissions but I don't know the way to replace them in another role ( there are a lot of permissions like objects,roles ecc..ecc..)
    Could you help me? 

    Thank you very much!!

    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