Return
-
Title
Cannot verify a SQL Server agent account in a Windows Active Directory (AD) group -
Description
A user cannot verify a SQL Server connection on one instance using a Windows AD group membership.The user is not a direct member of the instance, but the Windows group in Active Directory is. -
Cause
The current solution today doesn't grant permissions to a Windows AD group; this is by design. -
Resolution
WORKAROUNDUse a specific Windows user account instead of Windows group for this instance.STATUS
The Windows group should be added as login to SQL Server instance (the same way as adding a login for a windows user) then the user can grant sysadmin privileges to this login.
The T-SQL commands to add a login to a SQL Server instance and grant sysadmin privileges, should be executed by sql instance admin.
***If the SQL Server login (from desired windows group) already exists in the SQL instance, then step 1 should be skipped:
1 - if not created, create SQL Server login from windows user or group
USE [master]
GO
CREATE LOGIN [domain\name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
2 - Grant sysadmin privileges to login
USE [master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [domain\name]
GO