Issue Revoke before Grant

SQL Server Administration
1

I hope everyone's doing well. I had a one line script to assign access to something:

GRANT privileges ON object TO user;

My co-worker, Senior Database Developer said instead it should be:

REVOKE Execute ON object TO user;
GRANT Execute ON object TO user;

He said this was a 'safety' thing and that we should take the privilege away before granting it. Essentially a best practice thing.

I searched this up and couldn't find anything. Has anyone experienced this or heard of this before? i.e. requiring a Revoke before Grant

2

I've not heard of this before? What's the specific situation it is supposed to protect against?

GRANT is a little unusual in that it doesn't generate an error if you grant the same permission twice. If you try to add a user to role twice, you get an error.

I'm not coming up with anything ...

But please post their reasoning. I'm very curious :slight_smile:

3

Thankyou. It didn't make sense to me either. I hope to get more info.

4

It could be a good practice.

  1. User dthief was granted execute permission
  2. Now you want to get away from giving user specific permissions but use best practice group or role permission

So before giving permission to group revoke it for user dthief

User in your sample code might not be the same user.

5

The problem described is revoking it from a user before granting to the same user. If it's a different user then it's just revoking from one user and granting to a different user.

6

I guess I am not seeing the question as being for the same exact user🤓

7

Thankyou both. I don't think this would change anything but... It was actually involving a schema and a Database Role. And yes the same Role in both the Revoke and the Grant

REVOKE EXECUTE ON SCHEMA::[schema1] TO [DBRole1];
GRANT EXECUTE ON SCHEMA::[schema1] TO [DBRole1];

The rational provided to me was: A Revoke/Grant is more explicit than a Grant only.

8

different shops have their own "culture" of doing things. to me I have never ran into this type of approach. it is just DB Admin's way of doing things. nothing technically wrong with it, just the DB Admin way.

9

Thanks.
I'm not keen on doing things that are unsubstantiated. Then again no harm in this approach either

10

Hmm. In some shops I work in, we'd have a second event to reconcile for auditing purposes. That's about the only downside I see.

11

Not "DBA", "Senior Database Developer" said. Hopefully a DBA wouldn't do that; he/she would at least check for existence before issuing a REVOKE. (Yes, I am a "Senior DBA".)

12

It checks that both the Schema and Role exist first. I didn't post that as I didn't want to clutter the post. I was focused on the Revoke/Grant vs Grant only