Revoke and Grant Privileges on SQL Server Database I have a user that I want to grant all privileges on all database tables except for DROP, TRUNCATE, DELETE, INSERT and UPDATE.
The Database has over 300 tables and I can't do this one by one. I need something like:
REVOKE DROP, TRUNCATE, DELETE, UPDATE, INSERT ON DATABASENAME.* FROM USER1;

But its throwing an error when I put the DATABASE.* with * representing all tables.
REVOKE means is taking the privileges back. If you didn't give any permission to any user, you can not take a back.
You can use DENY, but not on the DROP and TRUNCATE. There is not a DROP and TRUNCATE permission just like you wrote.
DENY DELETE, UPDATE, INSERT ON DATABASE::[databasename] TO user1;

But, you should think about the privileges which are you want to grant and check out the database roles.