[SOLVED] Is there a resource in SqlServerDsc I can use to configure the ALTER TRACE server permission?

Is there a resource in SqlServerDsc I can use to configure the ALTER TRACE server permission?

Can I implement the following T-SQL code using a PowerShell DSC resource from the SqlServerDsc module?

USE master
GRANT ALTER trace TO [myUser]

I have tried the following without success:

SqlDatabasePermission Grant_Alter_Trace_Permission
{
    Ensure               = 'Present'
    Name                 = 'mySQLUser'
    Database             = 'master'
    PermissionState      = 'Grant'
    Permissions          = 'ALTER TRACE'
    ServerName           = 'sqltest.company.local'
    InstanceName         = 'MSSQLSERVER'
}

The resource fails with the following error:

PowerShell DSC resource DSC_SqlDatabasePermission failed to execute Set-TargetResource functionality with error message: System.InvalidOperationException: The name 'mySqlUser' is neither a database user, database role (user-defined), or database application role in the database 'master'.

Thank you for your help!

Solution

ALTER TRACE is a Server Permission not a database permission. So try SqlServerPermission instead of SqlDatabasePermission if such a thing exists.

You might be able to work around this by creating a user in Master for mySQLUser. My guess is that DSC is checking for the existence of a database principal before running

GRANT ALTER trace TO [MyUser]

And that grant will succeed if the login exists, if you can persuade DSC to attempt it.