0

I am having a very tough time figuring out the permissions in my database. My users gain access to the database through reports on SharePoint (via Impersonated authentication configured through Kerberos). Users, who are impersonated, are all added to AD Groups. And in my database, I am granting permissions to the AD GROUPS (as logins) and NOT to the individual users. I have 1000 users but 10 AD Groups. Each user is part of an AD Group. The users currently cannot have access to the database – they are only able to see the database if I was to add them individually as logins (obviously not an option). If I add their AD Group, it doesn’t seem like it works. Again, they are authenticated through Kerberos as impersonated accounts. Here is a map of what I’m saying: enter image description here IF the AD GROUP has permission, why doesn't the user within has permission?? I reviewed this question, but I'm not sure where is the equivalent for SharePoint Integrated mode.

Community
  • 1
  • 1
LearnByReading
  • 1,813
  • 4
  • 21
  • 43
  • Is your AD group designated as a security group in AD? You can check this by looking at the GroupCategory field in a call to `get-adgroup «your group name»` in powershell. If you don't have the AD PoSH module, you can check in the AD Users and Computers MMC snapin. Failing that, ask your domain admin. S/he will be able to tell you. – Ben Thul Nov 09 '16 at 21:23
  • @BenThul Hi Ben, Thanks for the comment. I checked, yes, they are assigned as "Security Group - Universal" – LearnByReading Nov 10 '16 at 14:44
  • Ah... interesting. When I've had this problem in the past, there's been it. If you impersonate one of your users with an `execute as login` statement, what did sys. login_token have in it? I'm guessing your group will be missing, but I'm curious what it does show. Maybe a sneaky deny... – Ben Thul Nov 10 '16 at 15:30
  • @BenThul indeed, when I get error messages, I get the account's username - which tells me the SSRS/SharePoint impersonation is delivering the AD Account and not the group. But what do you mean by "what did sys. login_token have in it" , how will I know that? Thanks again – LearnByReading Nov 10 '16 at 15:33
  • While impersonating the login, do a `select * from sys.login_token`. It will show everything that that login qualifies for (AD groups, server groups, etc). – Ben Thul Nov 10 '16 at 15:38
  • @BenThul all the usages are "GRANT OR DENY" but I'll try executing AS the logins in question. Thanks – LearnByReading Nov 10 '16 at 15:46
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/127831/discussion-between-ben-thul-and-learnbyreading). – Ben Thul Nov 10 '16 at 18:44

0 Answers0