I have a domain in which I have couple servers with dedicated SQL Server databases. I also have couple external companies that need access to this servers and databases on different permission level (even within single company).
At the moment I only have Remote Desktop access working by using a solution from this article:
I basically have one GPO for each company on each permission level like User and Admin. Inside GPO I’m assigning security group linked to a company members (for example “RDP Access – User – CompanyA”) to “Remote Desktop Users”. And I can drag and drop this GPO to particular OU linked to a computer. By that everyone inside company security group is also getting assigned to “Remote Desktop Users” and will get access to a RDP on that server.
I have trouble granting access to a database. I tried the same approach as for RDP – where I would make a GPO that would assign company’s security group to a group responsible for database access. But the problem is that there is no builtin group like that. So I tried to create my own security group, let’s say “Database User” that will be an equivalent for “Remote Desktop Users” but instead of RDP it would apply to the database. I then made a login inside my DB corresponding to that group following this article:
But this does not work and users can not connect to the DB even if they are in correct security group. Group “Database User” works by itself and I can assign users straight this group and they are able to connect. But it does not work when assignment is handled via GPO.
I suspect that this is a problem with “Remote Desktop Users” being local group on that particular computer whereas my group is only visible through domain (I can view “Remote Desktop Users” inside Local Users and Computers but there is no “Database User”).
Is there any way of granting database access through GPO in such scenario? If yes – how?
This is simplified example of my GPO structure: