Easy Way to GRANT/REVOKE Rights to Tables within a Microsoft SQL Server Database

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.