Access to SQL Server for Active Directory users and groups

When you are managing multiple SQL servers, it is handy to have the ability to give access to the SQL Server databases by using Active Directory users or groups. Of course, it should be also reviewed from the security point of view – not always it is a good solution.

In order to give Active Directory users or group access to the MS SQL Server, you should connect to the server using Microsoft SQL Management Studio. Once connected, expand Security -> Login and right-click on the Login element. From the dropdown menu, select New Login:

In the popup window, click Search button:

By default, only Users and Built-in security principals are in use, in order to be able to select groups, click on Object Types:

On the next popup, check the checkbox next to the “Groups” and click OK:

Currently, the user or group selection will be performed on the server only. In order to be able to select from the domain catalogue, click on Locations:

In the tree, you should see Active Directory you are connected to. Select and click OK:

Now, you can enter the user or group name and click “Check Names” to make sure that the desired user or group is found by the domain manager:

Once it is found, the underlined name is displayed and you can click OK to confirm your selection.

As the last step, you should select proper permissions – you may give the user or group permissions to access a particular database or set of databases, you can also select server roles in which the user should work.