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.
It's impossible to accomplish what you want by only granting/denying permissions.
To deny DELETE, INSERT and UPDATE it's sufficient to make a user the member of db_denydatawriter fixed database role, and even if the user has some of these permissions inherited from membership in other roles or windows groups mapped to this database deny will win.
But to allow any changes to your tables, your user has to have ALTER TABLE permission or be a member of db_ddladmin role.
db_ddladmin role gives you more than just altering tables, it gives your user the permissions to create/alter any other object(but not principal) in the database, not only tables, but maybe it's what you want.
In order to not allow DROP and TRUNCATE you should deny the same ALTER TABLE permission so here you should decide, or your user has this permission and can alter your tables, drop and truncate inclusive, or has not.
The solution here may be to use ddl-trigger where you manually rollback any DROP TABLE issued by unwanted user.
To change ddl-trigger your user should have ALTER ANY DATABASE DDL TRIGGER and it will not have it if it is only a member of db_ddladmin.
There is no way to disallow truncate table while preserving ALTER TABLE permission as ddl-trigger will not catch it.
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.
In you case, grant all privileges is not right choice, analyze what permissions needed for the particular user, and GRANT only required permissions.
Meanwhile, using following steps, you can achieve that you looking for as DENY take precedence over GRANT:
use YourDB;
Create user YourUser for login YourUser;
Alter role db_datareader add member YourUser;
Alter role db_ddladmin add member YourUser;
DENY CONTROL ON SCHEMA::dbo to YourUser; --- if all tables under different schema, change the schema name or repeat this for multiple schemas
