CODEWITHSUNDEEP
sqlsql-serverssms
user3325655
user3325655

Reputation: 225

Grant truncate permissions on all tables with out modify

Is there a way I can grant truncate permission to a user without altering privileges in SQL Server?

Upvotes: 0

Views: 5651

Answers (2)

Malakiya sanjay
Malakiya sanjay

Reputation: 208

Create a test Login and User id then grant it execute permission on the stored procedure Truncate_Table_Loner. This id will be used to perform the truncate.

-- Grant Execute Permission
-- Setup ID on Database with Connect permission
USE master 
GO
CREATE LOGIN [test_user_id] WITH PASSWORD = 'JustConnect123';
GO
USE TestSQL
GO
CREATE USER [test_user_id] FOR LOGIN [test_user_id];
GO
-- Grant Permission
GRANT EXECUTE ON dbo.Truncate_Table_Loner TO [test_user_id];
GO

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

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 are not 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.

CREATE PROCEDURE dbo.usp_Demo  
WITH EXECUTE AS 'CompanyDomain\SqlUser1'  
AS  
SELECT user_name();

Source

You can go through this official documentation.

Upvotes: 1

Related Questions