Post reply

Mysterious permissions issue - SELECT denied on UPDATE

  • April 4, 2019 at 10:10 pm

    #2812493

    Hi,

    I have been working all day troubleshooting a SELECT permission denied error, and at first I thought I just did something really silly, but I'm becoming more and more convinced there may be some bug in SQL Server.

    1. Executing the failed statement in SQL Server Management Studio using Run As Different User results in success

    UPDATE Attachments SET Attachment_Group_ID = 12447 WHERE ID = 2097

    2. Executing the failed statement in SQL Server Management Studio using EXECUTE AS LOGIN = 'DOMAIN\USERNAME' also results in success:

    EXECUTE AS LOGIN = 'MYDOMAIN\WebService';

    BEGIN TRANSACTION

    UPDATE Attachments SET Attachment_Group_ID = 12447 WHERE ID = 2097

    ROLLBACK;

    REVERT;

    3. Executing the failed statement in the User Acceptance Testing environment succeeds, whereas it fails in production. So it cannot be reproduced on a different server running an identical version of SQL Server (14.0.3048.4). Also, the User Acceptance Testing database is a snapshot of production from 9PM EST last night, so it is extremely similar to production.

    4. sp_column_privileges suggests there are no column-level privileges causing problems

    5. Using SQL Server Profiler and the "Audit Schema Object Access Event" event, I see two of these events emitted for the same TextData, and one has Success=1 and the other has Success=1. - This is my first time ever resorting to using this event. Previously, I had never needed anything more than the "User Error Message" event. Below is a literal translation of my SQL Trace into BBCode table format:

    10

    114

    114

    114

    114

    114

    114

    12

    EventClassTextDataApplicationNameNTUserNameLoginNameClientProcessIDSPIDStartTimeEndTimeBinaryDataDatabaseNameSuccess
    10exec InsertAttachment @Attachment_Group_ID=0,@Attachment_Type_ID=9,@UserName=N'John.Zabroski',@Attach_File_Path=N'Please remove.msg'WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PMApr 4 2019 4:59PM

    exec sp_reset_connection WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PMApr 4 2019 4:59PM

    exec InsertAttachment @Attachment_Group_ID=0,@Attachment_Type_ID=9,@UserName=N'John.Zabroski',@Attach_File_Path=N'Please remove.msg'WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PM

    IF NOT EXISTS (SELECT Attachment_Group_ID FROM dbo.AttachmentGroup WHERE Attachment_Group_ID = @Attachment_Group_ID)WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PM

    IF NOT EXISTS (SELECT Attachment_Type_ID FROM dbo.AttachmentType WHERE Attachment_Type_ID = @Attachment_Type_ID)WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PM

    INSERT INTO dbo.Attachment ( Attachment_Group_ID , Attachment_Type_ID , Attach_File_Path , Uploaded_By , Upload_Date ) VALUES ( @Attachment_Group_ID , -- Attachment_Group_ID - int @Attachment_Type_ID , -- Attachment_Type_ID - int @Attach_File_Path , -- Attach_File_Path - varchar(500) @UserName , -- Uploaded_By - varchar(50) GETDATE() -- Upload_Date - datetime )WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PM

    INSERT INTO dbo.AttachmentGroup ( IsDeleted ) VALUES ( 0 -- IsDeleted - bit )WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PM

    SELECT * FROM Attachment WHERE Attachment_ID = @@IDENTITYWebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PM

    UPDATE Attachments SET Attachment_Group_ID = 12468 WHERE ID = 2098WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PMApr 4 2019 4:59PMOperationalAnalytics
    13UPDATE Attachments SET Attachment_Group_ID = 12468 WHERE ID = 2098WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PMOperationalAnalytics
    162Changed database context to 'OperationalAnalytics'.WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PMOperationalAnalytics
    162Changed language setting to us_english.WebsiteWebServiceMYDOMAIN\WebService13640159Apr 4 2019 4:59PMOperationalAnalytics

  • April 4, 2019 at 10:18 pm

    #2812884

    It seems as though the BBCode formatting is jacked up after the forum upgrade...

  • April 5, 2019 at 2:54 pm

    #2867350

    Rebooting the server mysteriously fixed the problem.

    I am not sure I understand why - we used the following command during the day yesterday and it didn't solve the problem:

    repadmin /syncall

    Does anyone know why a reboot would fix this issue?

    It's almost as if changing a member from a Windows AD Group causes a duplicate login token to be added to sys.login_tokens. I think next time I encounter such a strange issue I will run the following command:

    EXECUTE AS LOGIN = 'MYDOMAIN\WebService'

    SELECT * FROM sys.login_token

    REVERT;

    Also, I spoke to a SQL Server MVP last night over drinks and he suggested grabbing the query execution plan to see if there are any statements in there that I wasn't seeing. Even though I don't have temporal tables or trigger features on this table, he mentioned looking at the execution plan would definitively reveal if there was any "spooky action at a distance" that I wasn't able to see from the plain text.

  • April 5, 2019 at 5:43 pm

    #2875776

    The one thing related that I can think of is if you were changing membership in AD groups, the user gets their windows token from the DC which includes their groups. The token won't get updated until the user gets a new token - usually by logging off and then logging back on.

    Sue

  • April 5, 2019 at 6:43 pm

    #2878605

    WOW! That makes a LOT of sense... we stopped the web service but MYDOMAIN\WebService was also connecting via a "Task Service" in addition to via IIS.

    I'll add these to my notes in the event (god forbid) I have to deal with this again. I feel very fortunate I was in an environment where we could reboot the server. Next time I may not be so lucky. I have posted a summary of my tricks here: https://dba.stackexchange.com/a/234046/35450

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply