0

In my understanding, in SQL Server, DENY blocks permissions and trumps any GRANTs that are in place. I was just thinking if there is a way to DENY the "with grant option", meaning, prevent any "with grant option" from being given to a principal explicitly, not by omitting it in the GRANT syntax as described in here. Something like "GRANT permission DENY GRANT OPTION" in pseudo-code.

I was looking for such a feature because I don't want a principal to be given a GRANT OPTION later by other principals having the same GRANT OPTION. I am looking at this issue from a security context.

Is there a way to do this?

Noble_Bright_Life
  • 569
  • 3
  • 9
  • 16
  • Huh? The default is that grant option isn't given. You have to *opt-in* to giving people the option to grant a permission to others. Why is this default behaviour not what you want? – Damien_The_Unbeliever Jan 14 '13 at 08:35
  • Thanks, I am aware of the default. I was just thinking of a scenario wherein another principal will grant a permission with grant option afterwards to the same principal that I have given the same permission but without the grant option. – Noble_Bright_Life Jan 14 '13 at 11:26
  • No, there's no option like that. Once you've delegated the permission decisions to another user, you can't stop who they further pass on that permission to, and whether they decided to delegate that authority further. – Damien_The_Unbeliever Jan 14 '13 at 11:35
  • Thinking out of the box perhaps you could have a stored procedure that rolls back the permissions that are granted by these users to another user. You could get this done by having a stored procedure or a shell script triggered by these changes. The only additional item you may want to do is lock the user account if they make the changes more than 3 times. – jjamesjohnson Jan 14 '13 at 21:15
  • @jjamesjohnson - Good one. Please put this as an answer so I can accept it. I think this is feasible as I currently capture the TSQL commands from all Server level and Database level DDL triggers. Thanks a lot. – Noble_Bright_Life Jan 15 '13 at 14:00
  • I mean I capture the TSQL commands from all Server level and Database level DDL **commands thru** triggers. – Noble_Bright_Life Jan 15 '13 at 14:26

1 Answers1

0

Thinking out of the box perhaps you could have a stored procedure that rolls back the permissions that are granted by these users to another user. You could get this done by having a stored procedure or a shell script triggered by these changes. The only additional item you may want to do is lock the user account if they make the changes more than 3 times.

jjamesjohnson
  • 639
  • 1
  • 7
  • 11