I grant database roles to our developers in the Azure sql databases. I need to DENY DELETE on all tables in the databases so I coded 3 ways below where X is an AD group. I am a member in X AD group.
2.DENY DELETE TO X; -- DENY to AD group
3. DENY DELETE ON ErrorLog TO X; -- DENY on table
then tested the delete after I ran each command. To my surprise, I can still delete records on errorlog table after all 3 statements ran!
PS: I only resorted to Statement 2 because Statement 1 did not work for me. Then coded statement 3 because 1 and 2 did not work.
Querying the DENY DELETE permissions:
SELECT l.name as grantee_name, p.state_desc, p.permission_name, o.name
FROM sys.database_permissions AS p JOIN sys.database_principals AS l
ON p.grantee_principal_id = l.principal_id
JOIN sys.sysobjects O
ON p.major_id = O.id
WHERE p.state_desc ='DENY' and l.name = 'X'
grantee_name| state_desc| permission_name |name
X DENY DELETE ErrorLog
X DENY DELETE sysrowsets
X DENY DELETE sysclones
What did I miss or have done incorrectly? Thank you very much!
Do you -- the user running the code -- also have sysadmin permission? If you have sysadmin permission, any DENYs are ignored.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 20, 2022 at 6:37 pm
Scott,
I ran
select HAS_DBACCESS('product')
select IS_MEMBER('db_owner')
and found I am a member of db_owner.
Thank you very much for the help.
Now I have to find someone not sysadmin, not db_owner to test the DENY Delete.
October 21, 2022 at 9:38 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply