Sql-server – Revoke and Grant Privileges on SQL Server Database

permissionssql serversql-server-2016

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.

Best Answer

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.

Related Question