All Forums Database
Alistair 14 posts Joined 08/08
30 Jan 2009
Revoking All Access Rights For Users

Hi,We're in the process of moving from our current access setup of granting access rights at user level to using Roles to control access. What we're struggling with is removing the existing rights users have (sometimes to dozens of DBs) in an easy way.Is there a way of revoking all the accesses they currently have in one go ? e.g.Revoke All on "EVERYTHING" from UserA ThanksAlistair

cnhan 11 posts Joined 12/07
30 Jan 2009

There's no such stmt. An alternative: drop user; then recreate the user.

rgs 106 posts Joined 02/07
30 Jan 2009

Hmm, is it not just this statement?:REVOKE ALL PRIVILEGES ON object FROM user_name;for exampe if user name is "sam":REVOKE ALL PRIVILEGES ON sam FROM sam;The only privileges it does not remove are INDEX and REFERENCES. See docmentation on REVOKE command for more specific information.

Alistair 14 posts Joined 08/08
06 Feb 2009

Thanks for that, we'd come to the conclusion that the only way to do it was to drop the users and recreate them.

super25 19 posts Joined 07/11
17 Apr 2013

is it possible to revoke access to everyone on a database?

17 Apr 2013

REVOKE ALL PRIVILAGES ON database_name FROM ALL.
Removes only the explicit privilages

You must sign in to leave a comment.