none
Permissions level denial for a sysadmin account? RRS feed

  • Question

  • Greetings. I work for a small automotive manufacturing plant. I am currently generating a file listing of a folder located in a secured network location only accessible to executive managment and our "Administrator" account. Our admin account is also sysadmin in our SQL server. 

    I've created a table titled "CCR_Filepath" in SQL. My plan is to have a daily job that will delete everything in "CCR_Filepath" and then reload all the file paths in the network location. Signing into SSMS as the administrator, I can insert values directly into the table, but I cannot get the filepaths from the network location. I have to use "EXECUTE AS USER = 'TESTCO\Administrator'" command to pull the filepath info from that network location, even though that's what I'm logged into SQL as; This is the only way I can generate the file path using this code:

    execute as user = 'TESTCO\Administrator'
    DROP TABLE #TEMPA
    DECLARE @FP AS NVARCHAR(150) 
    SELECT @FP = 
    '\\TESTCO\PLANT ADMIN\ACCOUNTING\Fixed Assets\CCR\CCR Copies\CCR 2019 Copies' 
    CREATE TABLE #TEMPA 
      ( 
         FILEPATH NVARCHAR(250), 
         D        INT, 
         F        INT 
      ) 
    INSERT INTO #TEMPA 
    EXEC XP_DIRTREE 
      @FP, 
      10, 
      1 
      SELECT CONCAT('\\TESTCO\PLANT ADMIN\ACCOUNTING\Fixed Assets\CCR\CCR Copies\CCR 2019 Copies\',FILEPATH) AS [FILEPATH], FILEPATH AS [DOCNAME] FROM #TEMPA


    Using this statement will load those values into my #TEMPA temptable. However, When I use the "EXECUTE AS..." statement I now get the following error :

    "

    Msg 229, Level 14, State 5, Line 2
    The INSERT permission was denied on the object 'CCR_Filepath', database 'Ignition', schema 'dbo'."

    This administror for testco is also a sysadmin, So I'm not entirely sure what's causing this issue? Running two query windows one with the execute as statement and the other without and running a third query window to show users active shows that both queries are using the exact same user, one being rejected. Anyone come across this before?

    Wednesday, May 1, 2019 2:52 PM

All replies

  • EXECUTE AS LOGIN = 'TESTCO\Administrator'

    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 1, 2019 3:34 PM
  • The execute as login works on the insert, but I cannot see the filepaths from the directory unless using the execute as user
    Wednesday, May 1, 2019 4:24 PM
  • Add 'TESTCO\Administrator' to your database users and then use EXECUTE AS USER = 'TESTCO\Administrator'.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 1, 2019 5:02 PM
  • That user is added and is sysadmin, which is why I don't understand why I'm getting the error
    Wednesday, May 1, 2019 5:37 PM
  • The result I would expect when you doing any form of impersonation is that xp_dirtree would not work.

    More precisely, when you impersonate a database user, that impersonation is only valid inside the database and not trusted on server level. (As that would permit for permission elevation.)

    When you impersonate a login, the impersonation is valid inside SQL Server, but not outside SQL Server. Again, that would permit for permission elevation. (That is, if you are only SQL admin, but not Windows admin, you could impersonate a Windows admin and gain access to things in Windows you should not have access to.)

    Apparently, xp_dirtree does not really play by the rules. Since you are impersonating yourself, you appear to get by when you do EXECUTE AS LOGIN.

    When you do EXECUTE AS USER, the context is not trusted, and presumably access is now made through the service account for SQL Server. Which apparently have access to the share. But as I said, context is not trusted outside the database, and this may explain the permission error. Anyway, I don't think you should use EXECUTE AS USER.

    If you don't see any files when you run this as yourself without any EXCECUTE AS USER, there are two possible reasons:
    1) You don't have permission on the share.
    2) There is a double-hop issue, because Kerberos is not set up appropriately.

    To test the latter, try running the script from SSMS directly on the server, instead from your own computer.

    One could also argue that a better solution would be a PowerShell script that read the share and stored the data in the table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 1, 2019 5:51 PM
  • Hi davealot98,
    From your error information, could you please try to run below T-SQL to grant user INSERT permission.
    grant insert on lgnition.dbo.CCR_Filepath to TESTCO\Administrator
    Hope it could help you.
    Best regards,
    Cathy Ji

    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, May 2, 2019 10:15 AM
  • Thank you very much for the reply. I'm logged in as our domain administrator which is why I also think it's something within SQL somehow degrading the permissions. I'm terrible with powershell commands but I will give that an honest shot today and hopefully resolve the issue outside of SQL. Thank you very much for your reply!
    Thursday, May 2, 2019 11:33 AM
  • Cathy, Thank you for the reply. Have tried this before posting (My apologies for not listing what all I've tried first) and still no fix. 
    Thursday, May 2, 2019 11:34 AM
  • Thank you very much for the reply. I'm logged in as our domain administrator which is why I also think it's something within SQL somehow degrading the permissions. I'm terrible with powershell commands but I will give that an honest shot today and hopefully resolve the issue outside of SQL. Thank you very much for your reply!

    Hi davealot,

    If you have resolved your issue, please close the thread by marking the useful reply as answer.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution.

    Best regards,

    Cathy Ji



    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

    Monday, May 6, 2019 11:30 AM