Solved

MS SQL 2014:  Allow access by security group

Posted on 2016-10-23
2
40 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 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 41855982
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 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41856630
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article runs through the process of deploying a single EXE application selectively to a group of user.
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 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