Adding AD group to SQL Server
Hi everyone. I know the topic has been covered, but I couldnt find solution to my problem.
The client used to grant permissions to database through single AD accounts and everything worked just fine. But the logical solution when there is a lot of users is to use groups. Client defined groups in Active Directory. Groups exist and members are assigned. The group has been added as SQL Server login(we can find the group in AD from SSMS, and add it as a login), which should grant group members access to server(we are talking about server access level, not database access). But it doesnt work. Accounts from group have no access.
At the login properties in Securables -> Effective tab there is an error message: Cannot execute as the server principal because the principal does not exist, this type of principal cannot be impresonated or you do not have the permission.
Such error does not appear for single accounts that has access to database. I'm running out of ideas here - what am I missing?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Give this a shot, to at least gather more information about why the group members cannot connect.
Regarding the error "Cannot execute as the server principal because the principal does not exist, this type of principal cannot be impersonated or you do not have the permission." -- That looks a lot like the error you get if you try to use TSQL to EXECUTE AS a windows group. Per BOL, you cannot impersonate a group.
However, you CAN impersonate a user IN that group, even if they don't have a server login for their own account. So, if YourDomain\\Bob is a member of YourDomain\\SQLPeople, and YourDomain\\SQLPeople is a windows authenticated login in SQL Server, then you can EXECUTE AS Bob, then check permissions from there. To wit:
USE master
GO
EXECUTE AS LOGIN = 'YourDomain\\AUserInTheADGroup'
SELECT * FROM fn_my_permissions(NULL, 'Server')
REVERT
At minimum, you should see the permission CONNECT SQL. If not, start troubleshooting here: http://dba.stackexchange.com/questions/4380/minimum-user-rights-for-connecting-ms-sql-database-remotedly
More information on fn_my_permissions: https://msdn.microsoft.com/en-us/library/ms176097.aspx
Longer description of impersonating a user to check their permissions: http://www.kendalvandyke.com/2008/12/hey-mr-dba-what-permissions-do-i-have.html
Point of clarification: I am assuming that when you say "SQL Server Login" you mean a login inside SQL Server that authenticates through Windows -- i.e., you did not specify a password. Logins with passwords are authenticated through SQL. For more, see: https://msdn.microsoft.com/en-us/library/ms144284.aspx
Check and see if the AD group has been added to a server group. It is possible to grant access to the SQL server without giving them access to the physical server itself.
If you want to give the group access to the physical server, then they need to either be added to an existing server group.
