none
Allow non-privileged Window Users to execute execute Stored Proc with OpenRowSet function

    Dotaz

  •  

    I want to allow non-privileged window accounts to run a stored procedure that uses openrowset (or a linked server … I’m flexible) to upload the excel data on a shared drive to SQL table.

    I’m able to get everything to run locally on the test server under a windows account with administrator rights.  But I’m not going to give administrator rights to the non-privileged window users.

    Information:

    • Server Information - SQL Server 2008 r2 running on Windows Server 2008 r2
    • Stored Proc #1 executes OpenRowSet function
    • Stored Proc #2 executes #1
    • SSRS Report calls Stored Proc #2
    • The SSRS Report is opened by the non-privileged window users. 

    The users are able to run other SSRS Reports that don’t use OpenRowSet as we have granted them Execute permission to the Stored Procs called by the SSRS report (securables).

    I have seen things like … “creating a "credential" from an account with local privileges and then granting that credential to the non-sysadmin account which will be calling the stored procedure with the OpenRowset() function call” but this is somewhat outside of my comfort level.  It would be great for someone to walk me thru this (if they think it will work).

    Thanks in advance

    Mark


    • Upravený Mark from NJ 9. března 2012 17:56
    • Přesunutý Tom Phillips 12. března 2012 14:21 Security question (From:SQL Server Database Engine)
    9. března 2012 17:51

Všechny reakce

  • Hi Mark:

    OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. To use the BULK option requires ADMINISTER BULK OPERATIONS permission.

    http://msdn.microsoft.com/en-us/library/ms190312.aspx 

    if you set up a linked server, this will possible with the help of remote mappings...

    I Hope the above information will helps u :)


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    11. března 2012 15:21
  • Satish -

    Thanks for the comment.  I know the Linked Server could help.  Unfortunately, I tried it and could not come up with the proper permissions settings to make it work either.  Suggests?

    Thanks, Mark

    11. března 2012 20:59
  • Hi Mark,

    I suggest you to see Ownership Chains in SQL Server.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    12. března 2012 7:27
    Moderátor
  • Hi Mark,

    You could try exploring the “EXECUTE AS” clause which can be used in stored procedures.

    http://msdn.microsoft.com/en-us/library/ms188354.aspx

    The procedure which uses the OPENROWSET could be executed as
    an ADMIN user and the non-Admin user will have access to execute it. That way you don’t have to assign any admin rights to the user.

    Try it if this works for you.

    Regards,

    Jerrin K



    Regards, JK

    12. března 2012 8:46
  • Thanks for the comments.

    Jerrin, I tried "Execute As" but didn't have much luck as the openrowset kept throwing errors, first it required me to make the database trustworthy (okay I can turn that off and on) and then another error ("Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".") which I haven't been able to work past.  Maybe I come back to it.

    Iric, the ownership chain is good info and another piece of the puzzle.  The OpenRowSet requires ADMINISTER BULK OPERATIONS permission which is a  server level permission.  I have played with giving Grant permissions to the User (caller) of the Stored Proc, but again to no avail.

    I think I need to start anew.  I read about using linked servers, credentials, and granting the user temporary sysadm privaleges (in code) before the stored proc is run and removing it (in code) after completion. 

    Thanks,

    Mark

    12. března 2012 13:33
  • Hi Mark,

    I guess your thinking is correct, I was discussed the same with one of my friend he also done the same thing, granting permission before inserting bulk data after the job completion revoke permission.

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    12. března 2012 13:58
  • Some clarifications here:

    Ownership chaining does not have much to do with the puzzle.

    ADMINISTER BULK OPERATIONS has nothing at all do with it, that applies when you use the special BULK provider.

    I will have to admit that I don't know exactly what applies here, but I would expect that that user needs to have file-system level permissions to the Excel file.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    12. března 2012 22:54
  • I tried putting sp_addsrvrolemember around the stored proc

    EXEC sp_addsrvrolemember'ARCHRECO\Domain Users', 'sysadmin'     

    EXEC    @return_value =  Canada.dbo.imp_Upload_List   

    EXEC sp_dropsrvrolemember'ARCHRECO\Domain Users', 'sysadmin'
     
    And it didn't work. 

    Erland, the users definitely have permissions to access the directory/file of the excel file.
     
    Maybe it has something to do with SSRS, and the account under which it runs (grabbing here).  The thing will run for me thru SSRS on the development server.  It doesn't work when I try to run it from a user machine and even my login which has administrative priviledges. I could set up a simple C# program to see if it will run from there.
     
    This is such a nice idea (to run user selected commands using SSRS) I hate to give up on it.  I'm just not that savy with SQL permissions to work thru it.
     
    As always thanks for the comments.
     
    Mark



    13. března 2012 3:17

  • I know nothing about SSRS, but if this runs from SSRS then it may be the service account for SSRS that needs permission to the file.

    I have some difficulties to follow all steps, but it seems that you should try one step at a time. It you are connected locally as sysadmin it works. If you are connected locally as a plain user, what happens and so on?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    13. března 2012 21:53
  • Erland - yeah that's good.  I'll try it.

    What I did ealrier today was to copy the excel file locally (onto the dedevelopment server).  It runs clean.  When we copy the excel file to the shared drive where it would normally reside it gives an error ("Cannot initialize the data source object of OLE DB provider \"Microsoft.ACE.OLEDB.12.0\" for linked server \"(null)\".).  I actually removed SSRS from the process and executed the stored proc from a C# program and got the above results.  Sounds like a permissions issues to me.  I'll keep plugging to pin point the thing..


    14. března 2012 3:11
  • So it comes down to this ...

    I am able to run the stored proc containing the OpenRowSet under a SQL login with SysAdmin privileges but not under a Domain Login with Sysadmin privileges.

    14. března 2012 13:30
  • Erland - yeah that's good.  I'll try it.

    What I did ealrier today was to copy the excel file locally (onto the dedevelopment server).  It runs clean.  When we copy the excel file to the shared drive where it would normally reside it gives an error ("Cannot initialize the data source object of OLE DB provider \"Microsoft.ACE.OLEDB.12.0\" for linked server \"(null)\".).  I actually removed SSRS from the process and executed the stored proc from a C# program and got the above results.  Sounds like a permissions issues to me.  I'll keep plugging to pin point the thing..

    this symptom and the solution is described in this blog

    http://beyondrelational.com/modules/2/blogs/66/posts/9999/openrowset-t-sql-sometimes-you-have-to-stop-and-smell-the-roses.aspx

    you need to read past the paragraph "(Insert long break here")


    14. března 2012 21:30
  • As always thanks for the comments.

    Apologies that I have not been able to get back to this issue as work has been busy.  I will look at this later in the week hopefully.

    Mark

    19. března 2012 12:53
  • @Daniel - i'm pretty sure the permissions on the TEMP directory is not the problem as the stored proc runs under the domain account with administrator privileges from SSMS and SQL Server Agent.  Thanks for the suggestion anyway.

    Mark

    20. března 2012 14:16