How to add Active Directory user group as login in SQL Server

In SQL Server Management Studio, go to Object Explorer > (your server) > Security > Logins and right-click New Login:

Then in the dialog box that pops up, pick the types of objects you want to see (Groups is disabled by default - check it!) and pick the location where you want to look for your objects (e.g. use Entire Directory) and then find your AD group.

You now have a regular SQL Server Login - just like when you create one for a single AD user. Give that new login the permissions on the databases it needs, and off you go!

Any member of that AD group can now login to SQL Server and use your database.

You can use T-SQL:

use masterGOCREATE LOGIN [NT AUTHORITY\LOCALSERVICE] FROM WINDOWS WITHDEFAULT_DATABASE=yourDbNameGOCREATE LOGIN [NT AUTHORITY\NETWORKSERVICE] FROM WINDOWS WITHDEFAULT_DATABASE=yourDbName

I use this as a part of restore from production server to testing machine:

USE masterGOALTER DATABASE yourDbName SET OFFLINE WITH ROLLBACK IMMEDIATERESTORE DATABASE yourDbName FROM DISK = 'd:\DropBox\backup\myDB.bak'ALTER DATABASE yourDbName SET ONLINEGOCREATE LOGIN [NT AUTHORITY\LOCALSERVICE] FROM WINDOWS WITHDEFAULT_DATABASE=yourDbNameGOCREATE LOGIN [NT AUTHORITY\NETWORKSERVICE] FROM WINDOWS WITHDEFAULT_DATABASE=yourDbNameGO

You will need to use localized name of services in case of German or French Windows, see How to create a SQL Server login for a service account on a non-English Windows?

Here is my observation. I created a login (readonly) for a group windows(AD) user account but, its acting defiantly in different SQL servers. In the SQl servers that users can not see the databases I added view definition checked and also gave database execute permeation to the master database for avoiding error 229. I do not have this issue if I create a login for a user.