Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_Blitz fails if user lacks alter trace rights and can't query sys.traces #3326

Closed
Montro1981 opened this issue Sep 5, 2023 · 1 comment
Assignees
Labels

Comments

@Montro1981
Copy link
Contributor

Version of the script
sp_Blitz 8.16
Date 2023-08-20

What is the current behavior?
sp_Blitz checks if the user has ALTER TRACE rights and sets @skiptrace accordingly.
In the TOURSTOP07 part of the script the "default trace enabled" is queried from the sys.traces which the user can view and we are ignoring the traces checks due to @skiptrace = 1.

In order to the check to be skipped the @SkipChecksDatabase, @SkipChecksSchema, and @SkipChecksTable need to be passed with check 106 present in the @SkipChecksTable.

If the current behavior is a bug, please provide the steps to reproduce.
Create a user on the SQL server with VIEW SERVER STATE rights in the db_data_reader role. Execute:
SELECT [path] FROM sys.traces WHERE [is_default] = 1;
This should fail.

What is the expected behavior?
Check 106 is added automatically added to the #SkipChecks table in TOURSTOP07 part:

INSERT #SkipChecks (DatabaseName, CheckID, ServerName)
SELECT
    v.*
FROM (VALUES(NULL, 106, NULL)) AS v (DatabaseName, CheckID, ServerName) /*alter trace*/
WHERE @SkipTrace = 1;

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL server 2017 (Web)

BrentOzar added a commit that referenced this issue Sep 10, 2023
Issue #3326 sp_Blitz fails on sys.traces
@BrentOzar BrentOzar added this to the 2023-10 Release milestone Sep 10, 2023
@BrentOzar
Copy link
Member

Thanks for the pull request! Looks good, merging into dev, will be in the next release with credit to you in the release notes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants