Solved

MS SQL 2014:  Allow access by security group

Posted on 2016-10-23
2
47 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Last week, our Skyport webinar on “How to secure your Active Directory” (https://www.experts-exchange.com/videos/5810/Webinar-Is-Your-Active-Directory-as-Secure-as-You-Think.html?cid=Gene_Skyport) provided 218 attendees with a step-by-step guide for…
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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.

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