Query Availability Group Permissions to CREATE ANY DATABASE

Question

Query Availability Group Permissions to CREATE ANY DATABASE

Crain, Brett 31

When configuring an availability group with automatic seeding, the secondary replicas must be given the following permission in order to automatically create/seed new databases.

ALTER AVAILABILITY GROUP [????] GRANT CREATE ANY DATABASE

Question: Are there any system views that can be utilized to query IF this configuration/permission has been granted? I'm working on validating the configuration of secondary replicas and wanted to include a check on this configuration setting. I've looked all over the sys.availability_* and permissions views and haven't found anything.

Thanks,

ChadP 10 Reputation points

2025-03-04T21:35:22.7866667+00:00

There is no way to query if that permission is granted? We're trying to configure availability groups with DSC and without a method to Test if the permission is granted, I don't see there is an automated way to configure the AG properly.
Dylan Janszen 0 Reputation points

2025-04-08T18:24:41.5366667+00:00

@ChadP I just encountered this exact same issue with DSC and upon googling immediately discovered this post. Did you happen to find a solution?

ChadP 10

@Dylan Janszen I had to create a DSC script that uses a file marker to know if the GRANT CREATE ANY DATABASE permission was given. It's not perfect, and I'm not happy about it, but it got me by the issue. Hope this will help you, too. Relevant portions of the Script resource below:

    TestScript = {
        $logFilePath = "C:\config\AGCreateAnyDB.log"
        $searchPhrase = "GRANT CREATE ANY DATABASE;"
        # Check if the file exists
        if (Test-Path $logFilePath) {
            $logFileContent = Get-Content -Path $logFilePath -Raw
            $phraseFound = $logFileContent -like "*$searchPhrase*"
            if ($phraseFound) {
                Write-Verbose "Found file C:\config\AGCreateAnyDB.log and appears to have assigned permissions"
                Write-Verbose "Delete this file if the operation should happen again, or inspect contents for errors."
                return $true
            }
            else {
                return $false
            }
        }
        else {
            return $false
        }
    }
    SetScript = {
        Import-Module 'SqlServer' -Cmdlet 'Grant-SqlAvailabilityGroupCreateAnyDatabase' -Force
        Get-PSDrive *>> $null
        If (!(Get-PSDrive -Name 'SQLSERVER' -ErrorAction 'SilentlyContinue' -Verbose)) {
            New-PSDrive -Name 'SQLSERVER' -PSProvider 'SqlServer' -Root 'SQLSERVER:' -Verbose
        }
        Grant-SqlAvailabilityGroupCreateAnyDatabase -Path "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\DEFAULT\AvailabilityGroups\SQLAG" -Verbose *>> c:\config\AGCreateAnyDB.log
        Return (Get-PSDrive -Verbose)
    }

Dylan Janszen 0 Reputation points

2025-04-10T16:42:57.4333333+00:00

That is a clever solution, but I guess the issue with that is that after some days/weeks, the log file may no longer be available?

What about a solution where you have a resource block that creates a table in master, a following resource block that applies the permission if the table is empty, and a 3rd and final resource block that depends on the 2nd and inserts a record into the table if one does not already exist.

On the first run, the table would be created, the setting would be applied, and the record would be inserted. If the record failed to insert, the grant permission would be applied a second or third time for example, but that is very unlikely and the command is idempotent so it wouldn't really matter. Assuming everything did complete, the grant permission resource block would check to see if the record existed and if so, do nothing.

Accepted answer

0 additional answers

Your answer

ChadP 10 Reputation points

2025-03-04T21:35:22.7866667+00:00

There is no way to query if that permission is granted? We're trying to configure availability groups with DSC and without a method to Test if the permission is granted, I don't see there is an automated way to configure the AG properly.
Dylan Janszen 0 Reputation points

2025-04-08T18:24:41.5366667+00:00

@ChadP I just encountered this exact same issue with DSC and upon googling immediately discovered this post. Did you happen to find a solution?
ChadP 10 Reputation points

2025-04-09T21:26:12.8266667+00:00

@Dylan Janszen I had to create a DSC script that uses a file marker to know if the GRANT CREATE ANY DATABASE permission was given. It's not perfect, and I'm not happy about it, but it got me by the issue. Hope this will help you, too. Relevant portions of the Script resource below:

TestScript = { $logFilePath = "C:\config\AGCreateAnyDB.log" $searchPhrase = "GRANT CREATE ANY DATABASE;" # Check if the file exists if (Test-Path $logFilePath) { $logFileContent = Get-Content -Path $logFilePath -Raw $phraseFound = $logFileContent -like "*$searchPhrase*" if ($phraseFound) { Write-Verbose "Found file C:\config\AGCreateAnyDB.log and appears to have assigned permissions" Write-Verbose "Delete this file if the operation should happen again, or inspect contents for errors." return $true } else { return $false } } else { return $false } } SetScript = { Import-Module 'SqlServer' -Cmdlet 'Grant-SqlAvailabilityGroupCreateAnyDatabase' -Force Get-PSDrive *>> $null If (!(Get-PSDrive -Name 'SQLSERVER' -ErrorAction 'SilentlyContinue' -Verbose)) { New-PSDrive -Name 'SQLSERVER' -PSProvider 'SqlServer' -Root 'SQLSERVER:' -Verbose } Grant-SqlAvailabilityGroupCreateAnyDatabase -Path "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\DEFAULT\AvailabilityGroups\SQLAG" -Verbose *>> c:\config\AGCreateAnyDB.log Return (Get-PSDrive -Verbose) }
Dylan Janszen 0 Reputation points

2025-04-10T16:42:57.4333333+00:00

That is a clever solution, but I guess the issue with that is that after some days/weeks, the log file may no longer be available?

What about a solution where you have a resource block that creates a table in master, a following resource block that applies the permission if the table is empty, and a 3rd and final resource block that depends on the 2nd and inserts a record into the table if one does not already exist.

On the first run, the table would be created, the setting would be applied, and the record would be inserted. If the record failed to insert, the grant permission would be applied a second or third time for example, but that is very unlikely and the command is idempotent so it wouldn't really matter. Assuming everything did complete, the grant permission resource block would check to see if the record existed and if so, do nothing.

Answer 1

Hi @Crain, Brett ,

Create any database is a proprietary Microsoft SQL Server permission, it is not associated with a user so it does not appear in sys.server_permissions.
You can check out this doc:
Use automatic seeding to initialize a secondary replica for an Always On availability group

If the availability group has not granted the correct permissions, you will see the error message in the SQL Server log of the secondary replica, if you want to verify that the automatic seeding is configured successfully, it also can be seen in the SQL Server log, and the DMV:
sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats.

to monitor seeding

-------------

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.

Share via

Query Availability Group Permissions to CREATE ANY DATABASE

0 additional answers

Your answer