Deny DELETE permission - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deny DELETE permission

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-11-02 : 13:56:45

Is there a way to revoke or deny DELETE permission on a specific table to all users except two, if a certain date column on the row the user is attempting to delete is not in the current month?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 13:59:07

I suspect only with a trigger or maybe a view.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-11-02 : 14:08:51

A trigger would be fine. A view is wrong because all users should be able to see all the information, just not delete a certain subset of it.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 14:16:24

I was thniking of something convoluted using the check option and a timestamp or last updated user column to prevent an update for these users. Hence the maybe.
Can't see how to make it work for a delete easily though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-11-02 : 15:44:34

Should this trigger:


CREATE TRIGGER trgTblUpd ON tbl FOR UPDATE
AS
IF UPDATE(DateCol)
BEGIN
IF EXISTS(SELECT * FROM Deleted WHERE (DATEPART(yyyy,Datecol) <> DATEPART(yyyy,getdate()) OR DATEPART(mm,Datecol) <> DATEPART(mm,getdate())) AND SUSER_SNAME NOT IN('Alloweduser1','AllowedUser2') ROLLBACK TRAN
END
RETURN

theoretically work?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-02 : 16:42:39

You specified DELETE permissions but your trigger is for UPDATE. Can you clarify the requirements?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-11-02 : 17:48:32

Ah, yes. Sorry, my mistake. It is for DELETE, not UPDATE.


CREATE TRIGGER trgTblDel ON tbl FOR DELETE
AS
IF EXISTS(SELECT * FROM Deleted WHERE (DATEPART(yyyy,Datecol) <> DATEPART(yyyy,getdate()) OR DATEPART(mm,Datecol) <> DATEPART(mm,getdate())) AND SUSER_SNAME NOT IN('Alloweduser1','AllowedUser2')) ROLLBACK TRAN
RETURN

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-03 : 12:03:16

Do you want to allow updates? If so

IF not exists (select * from inserted)
and EXISTS(SELECT * FROM Deleted WHERE (DATEPART(yyyy,Datecol) <> DATEPART(yyyy,getdate()) OR DATEPART(mm,Datecol) <> DATEPART(mm,getdate())) AND SUSER_SNAME NOT IN('Alloweduser1','AllowedUser2')) ROLLBACK TRAN

I usually include a raiserror as well.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-11-08 : 10:40:54

Updates are not an issue.

I'm trying to include a raiseerror, like so


CREATE TRIGGER trgPaymentsDelete ON Payments FOR DELETE
AS
IF EXISTS(SELECT * FROM Deleted WHERE (DATEPART(yyyy,DatePosted) <> DATEPART(yyyy,getdate()) OR DATEPART(mm,DatePosted) <> DATEPART(mm,getdate())) AND SUSER_SNAME NOT IN('sa','ba','ca')) 
	BEGIN
	RAISEERROR ('Permission denied to delete old payments', 16, 1)
	ROLLBACK TRAN
	END
RETURN

but darn thing won't compile. It's getting this error

Server: Msg 102, Level 15, State 1, Procedure trgPaymentsDelete, Line 6
Incorrect syntax near 'Permission denied to delete old payments'.

I'm also unsure if ROLLBACK TRAN should precede or follow RAISEERROR. If RAISEERROR is going to raise the error immediately on the client, which will then display the error message in a message box, and wait for the user to click it to dismiss it, and then the transaction will roll back, it's not good. But all the examples I've seen do this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-08 : 11:32:56

One issue is that SUSER_SNAME() is a function, so you need the parentheses. As to the Rollback location, I do the ROLLBACK first and then raise the error WITH NOWAIT.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-08 : 11:36:59

Also it's RAISERROR not RAISEERROR

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-11-08 : 11:39:46

Blimey, that's it! Never underestimate the power of bad spelling...

Thanks, Lamprey!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-08 : 11:50:10

np.