I want to change the permissions for all the tables in a SQL-Server database at once. Is there a way to do this?
views:
35answers:
3
+2
A:
Run the results of this script (change to suit your requirements):
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)
gbn
2009-12-28 16:58:41
A:
You can write a script that retrieves the set of tables and then grants/denies permissions through dynamic SQL.
However, I think a better approach would be to create a role, grant rights to that role, and then add/remove individuals from that role as needed.
Mayo
2009-12-28 16:58:52
+2
A:
Provided all of your tables belong to the same schema, you could modify permissions at the Schema level.
John Sansom
2009-12-28 17:29:57
ah, I always forget this option
gbn
2009-12-28 17:40:12
Also many GRANTs have an ALL/ANY option, there are database level permissions that imply permissions on every object in the database, and there are fixed roles like db_datareader/db_datawriter. In other words, there are many many ways to skin this one.
Remus Rusanu
2009-12-28 17:48:38
I guess what Remus is alluding to is that it's really about identifying what works best for your environment in terms of manageability.
John Sansom
2009-12-28 17:52:44