LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
0
Guests Online:
89

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Managing SQL Server Access with AD Groups

Please wait...

Managing SQL Server Access with AD Groups

Postby philhege on Wed Jan 08, 2014 1:37 pm

I have a new system in which I want to control access through Active Directory groups. I'm not having much success, hence this post.

Here's what I have so far:

1. An instance login that is the AD group.
2. A database user that mirrors the group.
3. A custom database role, of which the subject database user is a member.
4. Securable objects in the role. I've granted EXECUTE permission on a suite of stored procedures and functions to the role.

When a member user attempts to run one of the SPs, they are presented with an error message that states the user is denied EXECUTE permissions on the procedure. Interesting, since I explicitly grant execute through the role membership.

I've added dbo prefixes to all of the objects (https://connect.microsoft.com/SQLServer ... dows-group) and their references in code. Still no joy. (BTW our security model is NTLM, and I've verified that SQL Server resolves the user to the correct group.)

I'm interested in hearing success and/or horror stories, and of course a solution.


HILL!???!? WHAT hill? I don't remember any %*@&) hill!
User avatar
philhege
LTD Senior Moderator
LTD Senior Moderator
LTD Bronze - Rating: 9
 
Posts: 95
Joined: Tue Oct 09, 2007 5:55 pm
Location: Western New York
Unrated

Re: Managing SQL Server Access with AD Groups

Postby philhege on Mon Feb 03, 2014 9:24 pm

Um, not following your post, Adem. I couldn't find the word "move" in my original post.

BTW I resolved this issue. You also need implicit CONTROL permission to run a stored procedure; I had been denying it.


HILL!???!? WHAT hill? I don't remember any %*@&) hill!
User avatar
philhege
LTD Senior Moderator
LTD Senior Moderator
LTD Bronze - Rating: 9
 
Posts: 95
Joined: Tue Oct 09, 2007 5:55 pm
Location: Western New York
Unrated