Yesterday, a customer asked for help setting up an additional database user with only SELECT rights to his database. He had a lot of tables, so the easiest solution I came up with involved using the sp_MSforeachtable system stored procedure. Here’s the T-SQL code:
EXEC sp_MSforeachtable 'REVOKE ALL ON ? FROM username' EXEC sp_MSforeachtable 'GRANT SELECT ON ? TO username'
Make sure you replace username with the database username that you want the rights applied to.