SQL Server Permission

Question

I have a USE case regarding assigning a particular permission. Since the user needs to be granted only specific permission, we are encountering problem to provide that. Below is the use case:

Create a login,
Create a user within a login
Make this user available in all databases
Provide access to the user on all\specific tables
Grant select, update, delete and insert permission on all tables on all databases
Deny alter permission on all tables on all databases
Provide truncate permission to the user on all tables on all databases

As per microsoft's article for minimum permission required to truncate tables is alter which i can't provide. Below is the stored procedure created in master and it is not accessible in all other dbs:

CREATE PROCEDURE sp_TruncateTable @databaseName varchar(50), @tableName varchar(50)
AS
DECLARE @alenzi VARCHAR(2000)
SET @alenzi ='TRUNCATE TABLE ' + @databaseName + '..' + @tableName
EXEC (@alenzi )

Can anyone help me in achieving the use case and also making this stored procedure available in all dbs.

Answer 1

See:
https://www.mssqltips.com/sqlservertip/2583/grant-truncate-table-permissions-in-sql-server-without-alter-table/#:~:text=A%20truncate%20table%20stored%20procedure,the%20table%20to%20be%20truncated.

Answer 2

The way to solve this is to package the required permission in the stored procedure. You do to this by creating a certificate and sign the procedure with the certificate. Then you create a login or a user from the certificate and grant that user the required permission.

I have an article on my web site where I discuss this technique in a lot more detail: https://www.sommarskog.se/grantperm.html. It will take some time for you to digest the information, but security is not a trivial matter.

You will need to rewrite the procedure. Right now it is wide-open to SQL injection, and you cannot tolerate that when you put permissions in the procedure, as the permissions can be exploited. I can't say that I am happy with the procedure being server-wide, as you may have to pack quite hefty procedures. It may be better to one procedure per database, and apply to signing to those procedures, as then you "only" need to package ALTER permission.

Answer 3

Deny alter permission on all tables on all databases

TRUNCATE is not a simple DML command, it's DLL and equals DROP & CREATE, so it's an alter; a permission you deied.

Answer 4

Hi @Nidhi Singh ,

Please refer to this similar thread: https://stackoverflow.com/questions/60098823/grant-truncate-permissions-on-all-tables-with-out-modify
Hope this would be helpful for you.

Best regards,
Seeya

---

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".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.