May 21, 2009 at 12:57 am
Hi all.
This is probably an elementary question. I just always get very twisted around when trying to deal with permissions issues on my DB objects, so it's easier to ask.
I couldn't seem to find any kind of answer in the SQL help files.
We are creating a table to keep transactions for our payroll system, which is being upgraded from an earlier, very scary version!
What we want to do is have it run as basically a transactional record.
In the table will be userID, Date, leave type, days of leave
Negative numbers of days will represent Sick Leave, Annual Leave, etc. taken, while positive day amounts will represent accrued time.
What we want to do is lock down this table so that users cannot delete or edit existing records at all in this table, even if they have access to the SQL Server.
As such, I simply want to deny delete and update permissions for users on this table altogether. I don't want to do it at a user role level, but rather directly on the table as a whole.
If adjustments need to be made to these transactions, an additional positive or negative record will be added to make this correction, just like any financial system, so delete and udpates will be disallowed across the board.
I just can't for the life of me find the interface to allow me to do this!
Help!
May 21, 2009 at 3:08 am
Hi,
This link may be of interest to you.
May 21, 2009 at 6:11 pm
Thanks for the interesting article.
I think you're suggesting there I could use check constraints to disallow Deletes and Updates on the table. And if I just didn't do the same for inserts, I'd still be able to allow users to insert.
Sounds like an awful lot of trouble though. I thought there was a way to simply deny deletes and updates on a specific table, at the table level, rather than the row level.
May 21, 2009 at 8:19 pm
The best way to do this is to use the DENY statement. Refer to Books Online.
Deny takes precedence over Grant, so even if your users have been granted write rights to that table a "DENY UPDATE, DELETE ..." will take precedence, leaving them only Insert rights (and select if they've got read rights).
May 21, 2009 at 8:33 pm
Well, that's exactly what I did.
I took the role granted to all the users (and for testing purposes, for the role I have too, which is a bit more admin) and denied delete and update on the table.
However, when I go into the table in the SQL Management Console and try to delete or update records in this table now, I can do so. So it hasn't worked.
Just to make sure I got the right roles, I logged in as one of our users and was able to delete as them also.
I thought there was a way to do a blanket deny of delete, update, etc., on tables, regardless of role, but I can't seem to find that screen in the management console!
Thoughts?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply