Solved

MS SQL 2014:  Allow access by security group

Posted on 2016-10-23
2
33 Views
Last Modified: 2016-10-24
Hi,

I'm new to SQL.
Members of a certain AD Security group are to be granted privileges to access a certain SQL database.  

How do I do that?  In Management Studio all I can find is users, not groups.  Also they seem to be the sql-servers' local users...
Is there a comprehensive basic howto anywhere?

Thanks,
Ralph
0
Comment
Question by:Ralph Scharping
2 Comments
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 250 total points
Comment Utility
SQL Server security consists of two entites:

Logins and Users. You need first to create a login on the server level. Here you can select your AD group. Then you create a user in the appropriate database based on that login.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
In Management Studio all I can find is users, not groups.
You can add groups as well. I guess you're using only the GUI and not the SQL commands, right?
So in Security / Logins when you add a New Login you can type directly DomainName\ADGroupName in the Login Name field but if you want you can click in the Search button and then in the new Pop Up window you'll need to change the location to your Domain and add the Groups to the Object Type. Now you'll be able to perform a search for the desired AD Group Name.

If you want to be more familiar with T-SQL then in SSMS click in the New Query button and type the following:
USE [master]
GO
CREATE LOGIN [DomainName\ADGroupName] FROM WINDOWS WITH DEFAULT_DATABASE=[DatabaseName]GO

Open in new window

Replace DomainName\ADGroupName with the desired value and also DatabaseName with the default database name that those users should have.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Synchronize a new Active Directory domain with an existing Office 365 tenant
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now