-1

Situation:
I have various users that has been allowed READ ONLY access to a database. Is it possible to INSERT rows to database via Stored Procedure using a range in Excel?

If yes, Can anyone please assist me with a sample VBA or SQL script?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Mike
  • 129
  • 1
  • 2
  • 14
  • 1
    Yes, you can create a stored procedure that inserts rows into a table, and only give the user permission to read from the table and to execute the procedure. The requirement is that the owner of the procedure is also the owner of the table in question, in that case the permissions are not checked. – GSerg Feb 27 '19 at 09:48
  • @GSerg I'm a bit confused, maybe I just need a coffee, but if you are the owner of a table don't you have full access to that table anyway then? I find it strange to be the owner of a table but not having access to my own table. – Pᴇʜ Feb 27 '19 at 09:54
  • @Pᴇʜ If, as the owner of the table, you also create a stored procedure that inserts into that table, and grant `execute` on it to someone else, they will be able to call it and have it insert, even though they do not have a permission to insert directly. The procedure is executed in *their* security context, and the insert succeeds because the permission check is not performed (which is because you own the procedure and the table), not because they execute it "as you". – GSerg Feb 27 '19 at 09:58
  • @GSerg Ah, that's clear now. Thanks for that explanation. – Pᴇʜ Feb 27 '19 at 10:02

1 Answers1

-1

I am sorry to be the bringer of bad news, however the work-around you want cannot be done on T-SQL. from official Microsoft documentation

" Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables that are referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table..."

Eliy Arlev
  • 511
  • 1
  • 4
  • 14
  • You should have read a little bit further. *"However, if the same user owns the calling module and the module being called, EXECUTE permission checking is not performed for the second module."* That is, if the same user owns the stored procedure and the table, the permission check is skipped. – GSerg Feb 27 '19 at 09:50
  • @GSerg What I understand from the second paragraph, in simple words is: If I wrote the procedure and I called to execute it, then EXECUTE permission checking is not performed. Am I correct? – Eliy Arlev Feb 27 '19 at 10:15
  • 1
    No. If you wrote the procedure and you own the table, then `insert` permissions are not checked if someone else executes your procedure. – GSerg Feb 27 '19 at 10:17
  • @peh How would you suggest the data of excel cell is passed via execute statement. Any script example? – Mike Feb 27 '19 at 13:34