Let in your environment there are lots of SQL user and as a DBA you have to give permission to a specific user to truncate table only. The specified User have no select/delete/insert/update permission on table.
In this scenario we have to grant ALTER permission to that user.
USE [VirendraTest] — VirendraTest is here database Name
GO
GRANT ALTER ON [dbo].[TestTable] TO [Virendra] — Virendra is a User
GO
Hi,
About ALTER permission:
https://msdn.microsoft.com/pt-br/library/ms190273(v=sql.120).aspx
In order to grant permission to TRUNCATE, you also grant permission to: “altering, adding, or dropping columns and constraints, reassigning and rebuilding partitions, or disabling or enabling constraints and triggers.” – BOL
With another words…it isn’t only truncate permission. Dangerous approrach.
You can afford this using logical via procedure.
[]’s
LikeLiked by 1 person
Agree dear!
LikeLike