Our corporate hosting server has had restricted SQL DB permissions for a very long time, but one of our end-user systems has started requiring a new DB permission, INDEX, which none of them have. I can manually grant the INDEX permission to every single database one at the time, but is there a better way to grant every user that has permissions on a database an additional privilege on each database they have permissions for?
Example:
User A has SELECT, UPDATE, DELETE on 35 databases, but now needs INDEX as well. I could go to each database that user A has access to, and manually grant it INDEX permissions, but that is a time-consuming method, and there are hundreds of users and hundreds of databases.
I have tried to run GRANT INDEX ON . to 'user_a', but since the user is not the root user, it doesn't have permissions at all to many databases, nor any of the system databases. (nor do I want any my users except the root user to have access to any of the system databases)
The problem is compounded in our case because we have well over a hundred users with access to 1 - 100 databases each, that all need to be granted INDEX on all of the databases they currently have some level of permission on, but not on any database they don't already have some permissions on.
