sp_Blitz: Error reading sys.traces with specific permissions

[Tisit]

Version of the script 8.21 - top Github commit

What is the current behavior? User with VIEW SERVER STATE, db_owner on master database and without ALTER TRACE will encounter following error while running sp_Blitz:

Msg 8189, Level 14, State 6, Procedure sp_Blitz, Line 734 [Batch Start Line 13] You do not have permission to run 'SYS.TRACES'.

If the current behavior is a bug, please provide the steps to reproduce. Run sp_Blitz with user having VIEW SERVER STATE, db_owner on master database and without ALTER TRACE

What is the expected behavior? sp_Blitz executes without errors

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures? Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) Mar 19 2024 00:23:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: )

#############################################################

This is the most obscure one I found. But now it bothers me, since I know it exists. Seems we check permissions on sys.traces with following query:

SELECT * FROM fn_my_permissions(N'sys.traces', N'OBJECT') AS fmp
                WHERE fmp.permission_name = N'ALTER'

For user without any special permissions we get:

1. SELECT

For user with db_owner on master we get:

1. SELECT
2. UPDATE
3. REFERENCES
4. INSERT
5. DELETE
6. VIEW DEFINITION
7. ALTER
8. TAKE OWNERSHIP
9. CONTROL

It would appear ALTER is there so we are good. But actually we aren't. Select on sys.traces will still fail for that user. If we add sysadmin permissions:

1. SELECT
2. UPDATE
3. REFERENCES
4. INSERT
5. DELETE
6. EXECUTE
7. RECEIVE
8. VIEW CHANGE TRACKING
9. VIEW DEFINITION
10. ALTER
11. TAKE OWNERSHIP
12. CONTROL

We see 3 additional permissions: EXECUTE, RECEIVE, VIEW CHANGE TRACKING. And I believe these give permission to actually read sys.traces. But you may not see them. If we give user without any permissions ALTER TRACE, fn_my_permissions will only show he has SELECT.

In conclusion I believe we shouldn't use fn_my_permissions to check for this specific permission. I think direct check should be used. Something like:

BEGIN TRY
    SELECT 1 FROM sys.TRACES
    SET @SkipTrace = 0;
END TRY
BEGIN CATCH
    SET @SkipTrace = 1;
END CATCH

[BrentOzar]

You're putting a lot of thought into this - let's make sure we're all able to reproduce the problem. Can you write out a script that someone can simply hit F5 on, and see the error themselves? Like include creating the user with the exact permissions that you're worried about. That way we can also test to make sure the solution will actually work too. Thanks!

[Tisit]

Here is a self contained script. Asumes sp_Blitz is created in master database

USE master

CREATE LOGIN BlitzTest WITH PASSWORD = 'TestBlitz'
CREATE USER BlitzTest FOR LOGIN BlitzTest
GRANT EXECUTE ON sp_Blitz TO BlitzTest
GRANT VIEW SERVER STATE TO BlitzTest
GO

EXECUTE AS LOGIN = 'BlitzTest'

PRINT 'This works'
EXEC sp_Blitz
PRINT 'This worked'

REVERT

GO

ALTER ROLE db_owner ADD MEMBER BlitzTest

EXECUTE AS LOGIN = 'BlitzTest'

PRINT 'This does not works'
EXEC sp_Blitz
PRINT 'Doesn''t reach here'

REVERT

GO

--cleanup
REVERT
DROP USER IF EXISTS BlitzTest
DROP LOGIN BlitzTest

After execution I get following results: Quite clearly I hope it shows the first sp_Blitz execution went through. After user was added to db owners the second execution failed