March 27, 2012 at 10:02 pm
Dear All,
Is there any way to remove all "insert and update" permission of "Public role" for all objects (table and view) found in SQL securables?
Please advise.
Thanks
KK
March 28, 2012 at 9:18 am
The public Role, by default, does not have permission to insert or update tables or views. Did someone change the default permissions, granting things specifically to public?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 9:40 am
ugh, i hate it when people start adding permissions to public.
this script will generate the REVOKE commands for any permissions granted to PUBLIC to specific objects ;
from there you can sort them and only run the ones you want as a seperate process.
you really want to revoke them ALL, and create your own roles with the *right* permissions, and then add people to that instead of public.
example:
REVOKE DELETE on dbo.PPEXP TO public
REVOKE INSERT on dbo.PPEXP TO public
REVOKE SELECT on dbo.PPEXP TO public
REVOKE UPDATE on dbo.PPEXP TO public
SELECT
'REVOKE ' + convert(varchar(50),x.[Action])
+ ' on ' + x.[Schema]
+ '.' + convert(varchar(50),x.[Object])
+ ' TO ' + convert(varchar(50),x.[User]) COLLATE Latin1_General_CI_AS
FROM (
SELECT
u.name COLLATE Latin1_General_CI_AS AS 'User',
schema_name(o.schema_id) As 'Schema',
o.name COLLATE Latin1_General_CI_AS AS 'Object' ,
p.permission_name COLLATE Latin1_General_CI_AS AS 'Action'
--into tmp
FROM sys.database_permissions p, sys.database_principals u, sys.all_objects o
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.grantee_principal_id IN (0, 2)
) X
Lowell
March 28, 2012 at 6:19 pm
Thanks Lowell,
Just found a workaround for it.
exec sp_msforeachtable 'revoke insert on ? to Public'
exec sp_msforeachtable 'revoke delete on ? to Public'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply