Grant SELECT, UPDATE, INSERT, DELETE to all tables except 1 (or more) in SQL Server 2005

Question

The user I have should have access to all tables in a database - SELECT, INSERT, UPDATE, DELETE and EXECUTE (ASP code to blame :-P) except for 1 table e.g. users.

When granting db_datareader and db_datawriter this gives them full access to everything and removing the DELETE permission on the users table will not work.

There are over 60 tables and was looking for a quicker way than using SSMS to go through every table and do this.

How would I go about doing this?

Answer 1

You can explicitly deny permissions which should take precedence. The syntax is

deny delete on dbo.users to username

Answer 2

You can use a hacky cursor and sp_executeSQL (GRANT can't take a variable tablename)

declare ctable cursor for 
    select Name from sysobjects where type = 'u'

declare @Name sysname
declare @ExecSQL nvarchar(512)

open ctable
fetch next FROM ctable into @Name

while (@@FETCH_STATUS = 0)
    begin
        if (@Name <> 'MyTableToExclude')
        BEGIN
            SET @ExecSQL = 'grant SELECT on ' + @Name + ' to PUBLIC'
            EXEC sp_executesql @ExecSQL

... etc

        END
        fetch next FROM ctable into @Name
    end
close ctable
deallocate ctable

Minor ... note that you can't grant exec on tables ;)