Deny INSERT/UPDATE/DELETE to a particular schema

Question

Is always hard to me to manage roles and permissions with users connecting to SQL server with Windows authentication.

There are some users (not actually SQL users on SQL, are coming from active directory) connecting to a server with SQL 2014, they are all using windows authentication, I want them to avoid doing INSERT/UPDATE/DELETE in all tables with a particular schema but they can run stored procedures that can affect the tables on that schema.

I was thinking to create a server role and then apply that server role to each database and also for new databases created.

My idea is to create a server role, assign those users to that role and then assign the role to the different databases on the server.

Is that the right way to do it?

Answer 1

Usually you just put the users in an AD group and add the group to SQL as a named user.

Then grant the named user select/execute privileges by schema.

This is how you grant/revoke privileges.

Let's say we have a user abc and he uses schema xyz. Here is the syntax.

GRANT Insert ON xyz TO abc;

Replace 'GRANT' with 'Revoke' and 'Insert' with ('Update' or 'Delete') according to user requirements.

You can revoke all of three at once together too.

Revoke INSERT, UPDATE, DELETE ON xyz TO abc;