While implementing a planned new user for bulk updates, i got the failure report by that job. To investigate the reason when i scripted the job in SSMS query pane, i noticed that one of the stored procedures is generating the following error.
Privilege ALTER TABLE / ALTER VIEW / ALTER INDEX may not be granted or revoked.
It was a strange behavior because this stored procedure was updating a table whose update privileges were already granted to related user. When i examined the code i came to know that before starting updates in the procedure a statement was there to disable a trigger on that table. This ALTER TABLE statement was creating problem.
I was not willing to provide some extra permissions other than selected updates to job user. So how to get the job executed by providing minimal previlages to job user for altering table to disable the trigger.
For the solution i opened the properties frame of user in its related database folder.
And assigned a database role “db_ddladmin” to it. It allows the user to use DDL statements like ALTER TABLE, ALTER VIEW and ALTER INDEX. Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.