Check if a user can run the RECONFIGURE statement-CodePudding

I'm using SQL Server Express on AWS and, when trying to change the MAXDOP via a T-SQL query, I got an error stating

Database Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]User does not have permission to perform this action. (37000: 15,247) ... You do not have permission to run the RECONFIGURE statement (37000: 5,812).

I suppose this can also happen on SQL Server instances that are not on AWS, it seems to be a user permission problem.

Is there a way to programmatically check if a user can (or cannot) run the RECONFIGURE statement, preferably running a T-SQL query on SQL Server >= 2014?

CodePudding user response：

The RECONFIGURE statement requires the server-level ALTER SETTINGS permission. The fn_my_permissions TVF can be used programmatically to check if the current user has the permission:

IF NOT EXISTS(
    SELECT 1
    FROM fn_my_permissions(NULL, 'server')
    WHERE permission_name = N'ALTER SETTINGS'
    )
BEGIN
    PRINT N'Current user does not have ALTER SETTINGS permission needed for RECONFIGURE statement';
END;

Alternatively per the comment by @lptr, one can use the HAS_PERMS_BY_NAME scalar function:

IF HAS_PERMS_BY_NAME(NULL, NULL, 'ALTER SETTINGS') = 0
BEGIN
    PRINT N'Current user does not have ALTER SETTINGS permission needed for RECONFIGURE statement';
END;