Hello experts,
I'm having trouble with this issue. I'm sure there must be a post about it already, but I can't narrow down my search to find the exact question and resolution steps. Here it is.
1. A few months ago, I received a request to deny delete to a group (Windows AD login) against 3 databases. So I issued a DENY DELETE to the relevant Windows login.
2. Now, someone is having trouble updating data. The issue seems to be that a trigger fires during the update. In the trigger the code tries to delete from a table in the previously delete-denied databases. So the update fails.
-- In retrospect, perhaps I should have used REVOKE - not sure, but I would be grateful if someone could confirm the proper convention for this kind of denial.
-- Also, I can't seem to find a listing of the DENY permissions - or rather, how to list what permissions are denied. I guess maybe that is a hard thing to do if the result of a DENY is to leave certain permission columns NULL (vs adding 'DENY'). But it would be helpful for me to see what the current permission state is.
-- Finally, I tried to undo the DENY without success. Here is my attempt, warts and all.
a. I tried GRANT DELETE... - no luck
b. Then I saw an article explaining to use REVOKE, so I tried 'REVOKE DELETE' followed by 'GRANT DELETE' - still no luck.
How can I untie this Gordian knot of permissions?
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 18, 2014 at 11:23 pm
2. Now, someone is having trouble updating data. The issue seems to be that a trigger fires during the update. In the trigger the code tries to delete from a table in the previously delete-denied databases. So the update fails.
I have a little confusion about the trigger that you are talking about in the above mentioned point. Is this a constraint in your application.
I did a test setup, Made a new database a new login and denied delete on this login.
I was able to update the data without any issues.
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
DENY is removed with REVOKE.
Here's an example:
USE tempdb;
GO
-- Create a dummy user
CREATE USER someUser WITHOUT LOGIN;
GO
-- GRANT DELETE
GRANT DELETE TO someUser;
GO
-- REVOKE DELETE
DENY DELETE TO someUser;
GO
-- only one between GRANT and DELETE
-- can exist at the same level
SELECT *
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID('someUser');
-- Remove the DENY
REVOKE DELETE FROM someUser;
GO
-- Grant
GRANT DELETE TO someUser;
-- Now you should see the permission granted
SELECT *
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID('someUser');
Basically, if you want to see which permissions are DENYed to a user, here's the query to use:
SELECT *
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID('someUser');
AND state_desc = 'DENY';
Then you just have to REVOKE the DENY and then GRANT it back.
Actually a simple GRANT would do.
Hope this helps
Gianluca
-- Gianluca Sartori
December 19, 2014 at 7:49 am
Thanks, Gianluca! That was the information I needed.
Thanks also Shafat for your response.
Sincerely,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply