I wish to alter the permissions for the tables inside a SQL-Server database at the same time. It is possible to method of doing this?
Run the outcomes of the script (change to match your needs):
SELECT 'GRANT SELECT ON ' + OBJECT_NAME(o.object_id) + ' TO myRole' FROM sys.objects o WHERE OBJECTPROPERTY(o.object_id, 'IsMSSHipped') = 0 AND OBJECTPROPERTY(o.object_id, 'IsTable') = 1 ORDER BY OBJECT_NAME(o.object_id)
Provided all your tables fit in with exactly the same schema, you can modify permissions in the Schema level.
You are able to write a script that retrieves the group of tables after which grants or loans/declines permissions through dynamic SQL.
However, I believe a much better approach is always to produce a role, grant privileges to that particular role, after which add/remove people from that role when needed.