SQL Server 2019 Permissions issues

Question

I am using sql server 2019  and I granted a user the DDLadmin role and the DBO role within a database they are able to create a stored procedure, but when they go to alter it they get the below error.  They are only able to alter it when they have the SA role.  They should be able to alter it without SA or DBO by granting them alter to the schema and stored procedure but it does not work.

 

Msg 3701, Level 14, State 20, Procedure spPSC_ARForecastByInvoice-ked, Line 3 [Batch Start Line 7]

Cannot alter the procedure 'spPSC_ARForecastByInvoice-ked', because it does not exist or you do not have permission.

Answer 1

Hi @Kevin Dennis

Please verify that the stored procedure is not hitting a linked server which may contain/use the credentials indicated.

Stored procedures validate connections/credentials on recompile.

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".

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.

Answer 2

Have you checked if you have any DENY in the database? Keep in mind that DENY takes precedence over GRANT.

SELECT *FROM sys.database_permissions WHERE state_desc = 'DENY'