I am trying to grant rights to an Active Directory Group on a Linked Server. I am unable to do it; it only allows me to grant rights to individual active directory users. Does anyone know how to achieve this?
1 Answer
You can do by creating a role for the AD Group user and assigning permissions to it.
Create your Active Directory Group as a login in SQL Server as below :
USE master; GO CREATE LOGIN [COMPANYDOMAIN\ADGroup] FROM WINDOWS; GO
Then create a user from the login in the database
USE DatabaseName; GO CREATE USER [COMPANYDOMAIN\ADGroup] FROM LOGIN [COMPANYDOMAIN\ADGroup]; GO
Now Grant appropriate permissions to the user
Note: Granting a server permission to a database role is not possible
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER to [COMPANYDOMAIN\ADGroup];
-
Doesn't this grant permissions to see the available linked servers, rather than grant permissions to use a specific linked server? Commented Aug 29, 2018 at 18:33