Sql-server – Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

sql serversql-server-2008

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should be in good shape. I can have the procedures handle all the audit logging, wrap things in transactions as needed, etc.

But to do this, I need to deny direct DML statements to everybody on this particular schema – even the sysadmin server role. Is there any simple way to do this without resorting to INSTEAD OF triggers (which wouldn't be quite as bulletproof)? I don't see any syntax like "DENY INSERT, UPDATE, DELETE ON SCHEMA::schemaname TO ALL", and denying permissions to the "public" role doesn't seem to have the desired effect.

Best Answer

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...

Related Solutions

Sql-server – Set permissions on all objects for SQL users

Single line to give permissions at the schema level to a role

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

And a second line to add users to the role

EXEC sp_addrolemember 'SomeRole', 'whatever user'

And do this in model so all new databases inherit.

Reasons, you should set permissions once only:

A schema is a container for objects.
New objects inherit permissions from the schema
A role is container for users
New users are added to a role and inherit

As you've found, migrating or restoring a database can lose object permissions when assigned directly to users. So why put yourself in that position?

You can also CREATE LOGIN with a SID so it is the same on all your servers and you don't get orphaned users either.

If you'd asked the correct question we could have saved you some coding...

Personally and finally, I'd say this is bad practice on "need to have", not "blanket do anything"

Sql-server – Deny drop permissions for all other users except actual administrator – SQL Server 2008

Use a DDL trigger to prevent the drop if the login name doesn't match what you expect. Modify the database name and login name as appropriate.

USE myDB
GO

CREATE TRIGGER no_drop ON DATABASE FOR DROP_TABLE
AS
IF SUSER_SNAME() != 'DOMAIN\username'
BEGIN
    RAISERROR('No table dropping.', 16, 1)
    ROLLBACK TRANSACTION
END

Best Answer

Related Solutions

Sql-server – Set permissions on all objects for SQL users

Sql-server – Deny drop permissions for all other users except actual administrator – SQL Server 2008

Related Question