Share via

SQL Server command permission levels

Stephen Schleyer 60 Reputation points
2024-07-02T13:10:17.5033333+00:00

Our COBOL IO modules use SQL Server commands to access table data. We are migrating to a new server and decided to tighten security by giving users minimum level permissions. This caused an issue because all of our IO modules use the TRUNCATE command to clear a table. The TRUNCATE command requires table-owner or sysadmin permission. Since our tables are relatively small, we can use the DELETE command to clear a table with no significant impact.

I was looking for a list of SQL commands that would require that higher level permissions to see if we utilize any of them in our source code.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,269 questions
0 comments
Accepted answer
  1. Erland Sommarskog 106K Reputation points MVP
    2024-07-02T13:55:55.5+00:00

    Basically, anything beyond EXECUTE, SELECT, INSERT, UPDATE, DELETE and MERGE requires something I would call an elevated permission.

    As Zahid says, for TRUNCATE TABLE, you need ALTER permission. That is definitely to be considered as an elevated permission.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Zahid Butt 641 Reputation points
    2024-07-02T13:39:14.3033333+00:00

    Hi @Stephen Schleyer,

    You may grant alter permission on required table for Truncate (user need not be the owner or sysadmin ). Just use below command:

    grant alter on TableName to UserName

    Secondly go through below link , it has sufficient info:

    https://www.sqlsplus.com/assigning-permissions-and-roles-in-sql-server/

    Regards,

    0 comments
  2. LiHongMSFT-4306 25,571 Reputation points
    2024-07-03T01:43:07.1033333+00:00

    Hi @Stephen Schleyer

    Here is a query to check if Truncate or Delete are used in the definition of SP, views or functions.

    SELECT o.name AS ObjectName,
       o.type_desc AS ObjectType,
       m.definition AS ObjectDefinition
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%TRUNCATE%' OR m.definition LIKE '%DELETE%'

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments