Automatic Seeding – Which Replicas Have Permission?

There has, for whatever reason, been a recent craze to figure out if a certain replica does or does not have the availability group create database permission. This is the permission that allows for automatic seeding to take place on the replica that is part of the availability group.

I don’t quite understand the need. If it shouldn’t have it, you can run the deny. If it should have it you can run the grant. My only guess, currently, is for some type of yet unknown auditing requirement – though, even here just running the commands should level set.

In order to query the system table that the information is stored in, you’ll need to access it via the DAC, which isn’t ideal. Additionally I want to point out that this is **completely** unsupported, undocumented, is subject to change at any point, and in general should not really be used… but let’s face it, for whatever reason you’re already here and any warnings are just going to be ignored.

SELECT	ags.name AS [AG_NAME],
		CASE 
			WHEN slo.intprop = 0 THEN 'DENY'
			WHEN slo.intprop = 1 THEN 'ALLOW'
			ELSE 'UNKNOWN'
		END AS [GRANT_CREATE_ANY_DATABASE_TO_AG]
FROM sys.sysclsobjs slo
	INNER JOIN sys.availability_groups ags
		ON slo.name = ags.name
WHERE class = 67