Deny delete for some users

Question

Deny delete for some users

krypto69

SSChampion

Points: 13604
More actions
November 8, 2017 at 9:52 am

#409756

Hi,
I have an application that there are mystery deletes occurring (long story). My users would like if there was a way for me to deny the ability to delete on the SQL DB and then via a SQL script be temporarily enable these user to do deletes.
Is this possible? What are my options?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

How are your users connecting to the SQL database? Do the users connect via Intergrated Security through the application, or does that application have its own Login that fulfils the requests of the user's using the application?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

Yes if you know the login/logins at fault you can certainly script out the grant/deny permissions on the objects in question.

krypto69 SSChampion Points: 13604 More actions · Answer 3

The users connect via integrated security.

Was thinking I could something like:

DENY DELETE ON Customers to Joe, Mary

GRANT DELETE ON Customers to Joe, Mary

Thom A SSC Guru Points: 99130 More actions · Answer 4

Granting a user permission to do something when they have a deny permission will not work. DENY > GRANT. For that tio work, you would need to REVOKE the user's permission and then GRANT the user's permission.

One idea I have would be to create a Role and add those users to that role. That role has the DENY permission on DELETE, while all the users have the GRANT permission on DELETE. Then, if a user (albeit temporarily) requires permission to DELETE, remove them from the role and add them back afterwards.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Thom A - Wednesday, November 8, 2017 11:03 AM
Granting a user permission to do something when they have a deny permission will not work. DENY > GRANT. For that tio work, you would need to REVOKE the user's permission and then GRANT the user's permission.
One idea I have would be to create a Role and add those users to that role. That role has the DENY permission on DELETE, while all the users have the GRANT permission on DELETE. Then, if a user (albeit temporarily) requires permission to DELETE, remove them from the role and add them back afterwards.

I like that idea with the roles. Modifying membership for the roles seems cleaner than messing around with object level permissions.

Sue

krypto69 SSChampion Points: 13604 More actions · Answer 6

Thom A - Wednesday, November 8, 2017 11:03 AM
Granting a user permission to do something when they have a deny permission will not work. DENY > GRANT. For that tio work, you would need to REVOKE the user's permission and then GRANT the user's permission.
One idea I have would be to create a Role and add those users to that role. That role has the DENY permission on DELETE, while all the users have the GRANT permission on DELETE. Then, if a user (albeit temporarily) requires permission to DELETE, remove them from the role and add them back afterwards.

Thanks Thom - I'm gonna give that a shot.