Sql-server – Deny insert/delete for all users in SQL Server 2005

deleteinsertsql server

In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one database.

What would be easiest way to deny insert/delete access for some tables (not all)? All users have db_datareader, db_datawriter.

Best Answer

It would be confusing if you granted someone db_datawriter and then denied them delete rights. So if you can, remove them from the db_datawriter and then grant them the rights they need:

create role role_MyApp
grant select, insert on Table1 to role_MyApp
...
exec sp_adduserrole 'role_MyApp, 'User1'

Related Solutions

SQL Server 2008 – Deny INSERT/UPDATE/DELETE for Specific Schema

If all data access is via stored procedures, then permissions on the table are not checked of the same user (dbo probably) owns both procedure and tables. This is called ownership chaining

This also means the no permissions are needed on the tables at all. You don't need to deny or grant anything on the tables because they won't be checked. Lack of GRANT means no rights anyway, so you don't need DENY

Now, this means nothing to folk with elevated privileges: sysadmin, db_owner etc. These will always have rights. You can only use triggers to block these: but they can disable or drop triggers of course.

I assume that your "end users" are not running as db_owner or sysadmin...

SQL Server – Deny Access to Information Schema

You should be able to just deny permissions on the entire sys and information_schema schema as a whole:

DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]

That should basically just prevent that user from doing any selects in those two schemas.

Best Answer

Related Solutions

SQL Server 2008 – Deny INSERT/UPDATE/DELETE for Specific Schema

SQL Server – Deny Access to Information Schema

Related Question