Only Truncate Table permission to specific user

Posted: November 4, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,

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

 

Comments
  1. Renato Siqueira says:

    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

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s