CODEWITHSUNDEEP
sql-serverdatabasepermissions
dmr
dmr

Reputation: 22383

Is there a way to change permissions for all the tables in SQL-server database at once?

I want to change the permissions for all the tables in a SQL-Server database at once. Is there a way to do this?

Upvotes: 2

Views: 4122

Answers (3)

John Sansom
John Sansom

Reputation: 41879

Provided all of your tables belong to the same schema, you could modify permissions at the Schema level.

See Grant Schema Permissions

Upvotes: 2

Mayo
Mayo

Reputation: 10812

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.

Upvotes: 0

gbn
gbn

Reputation: 432421

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)

Upvotes: 2

Related Questions