I have a server with multiple databases attached that I would like to have a certain group being able to view their respective databases only, such as [DOMAIN\Group1] being able to only see Database1, [DOMAIN\Group2] being able to only see Database2, etc.
For the purposes of this example, I have set up a AD group called [DOMAIN\GroupName].
With the group, I have added it to the security logins and have run the following to deny view capabilities:
DENY VIEW ANY DATABASE to [Domain\GroupName].
The next step I'd like to do is to give view access to each group to their own databases by using the following:
ALTER AUTHORIZATION ON DATABASE:BName TO [Domain\GroupName], however, it gives the appropriate error "An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys." This works when using it for specific USERS but not groups.
With that, how do I accomplish this? The reason this is being done is that we do not want to specify every single time within sql who has access to what when a new user is added or removed, this will and should be handled by AD and security being already in place on the sql side by using the group name.