Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL 2014:  Allow access by security group

Posted on 2016-10-23
2
Medium Priority
?
64 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 35

Accepted Solution

by:
ste5an earned 1000 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 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Here's a look at newsworthy articles and community happenings during the last month.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

715 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