Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL 2014:  Allow access by security group

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

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 53

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

580 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