I have an Azure Function that has managed identity enabled, and we run these commands to grant the Azure Function's managed identity member as follows:
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
Now this allows the Azure Function to run this SQL statement:
DELETE FROM Logs
but this will raise an error that table is not found, and permission issue, when trying to run:
TRUNCATE TABLE Logs
Any advice how we can allow the Azure Function to do a TRUNCATE?
If we take a look at the documentation at MS Learn, particularly the permissions section, then you will find this:
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and aren't transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
So the question may be why does truncate require alter privilege rather than delete permission that is needed for the delete command? I did not participate during the decision and I try to avoid providing an opinionated response, but a good possible reason as of why alter permission is needed is that the identity column sequence is changed, which makes it technically an alter:
If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.
So one could make a case for the alter privilege being necessary because the identity is altered.