locked
SQL Server 2008 Permissions granted to group do not work RRS feed

  • Question

  • Warning: I'm semi-new to SQL server.  I have had some experience with it, but not all that much.  

    I have a situation where I have granted permissions to a group using SSMS, copied from a previous user who had the right permissions, but the user in that group is unable to access the table needed.  (The user did reboot her system after I added her to the group and the rights were assigned.)  If I assign the same permissions directly to that AD user account, everything works fine.  Of course, I do not want to leave it that way, and would rather have the permissions granted through the group for future employees in that department, and clean up our permissions on the databases.  Most of the permissions on that SQL server and the 3 databases on it are assigned to groups and work fine.  It is just this one group where the permissions aren't going through properly.  

    Is there something I'm missing?  Some process in SQL Server I have to do to make the permissions effective?

    Tuesday, November 28, 2017 5:33 PM

All replies

  • Hi dgingeri,

    >>If I assign the same permissions directly to that AD user account, everything works fine.

    I’m not sure how you configuration looks like, but I’d suggest you perform a quick check with the steps below:
    1. Verify your windows account’s permission path with the statement below.
    2. Check the effective permission on the objects.
    ----STEP1 
    EXEC master.sys.xp_logininfo @acctname = 'WINDOWSACCOUNTNAME',@option = 'all';
    GO	
    ----STEP2
    EXECUTE AS LOGIN = 'WINDOWSACCOUNTNAME';
    GO	
    ----Check effective permission on a object
    SELECT *
    FROM fn_my_permissions('SCHEMANAME.OBJECTNAME', 'OBJECT');
    GO




    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 29, 2017 2:43 AM
  • I fail to understand what you're saying here.
    Wednesday, November 29, 2017 4:55 PM
  • Hi dgingeri,

    I apologize for the confusing, is that possible your AD account belongs to more than one AD groups so it receive different permissions from them?

    For example, your account belongs to two AD groups(GP1 and GP2). You have granted SELECT permission to GP1 on your target table, but GP2 is a member of db_denydatareader database role. In that case, the db_denydatareader would override the SELECT permission on the table(from GP1), that might explain the issue you are facing.

    In this case, I’d suggest you use the script in step1 to find out how your AD user inherits permission from AD groups, then use the script in step2 to check your AD user’s effective permission. If you are sure you have granted proper permission to the AD group and step1 returns more than one row, then you are facing the exact issue as described in above section.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 30, 2017 7:23 AM