Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016
    Posts
    1

    Unanswered: Grant view Database to Group

    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.

    Thanks in advance.

  2. #2
    Join Date
    Jun 2013
    Posts
    10
    I haven't done this before, but have you tried assigning the AD group to a role and granting access to the database that way?

    https://msdn.microsoft.com/en-us/library/ms187936.aspx
    https://msdn.microsoft.com/en-us/library/ms189775.aspx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •