Sql Virtual Table with Read Only Access: insert, delete , update?

Question

2 questions:

Will i be able to create a virtual table using a user that has read only access to a database ?

ex:

DECLARE @tblStudentDtl TABLE(
SrNo int IDENTITY not null,
sName varchar(50) ,
sAddress varchar(100),
ClassName varchar(100)
)

Will i be able to insert, delete, and update the data from a virtual sql table that i create using a user that has read only access to a database ?

ex:

DECLARE @tblStudentDtl TABLE(
    SrNo int IDENTITY not null,
    sName varchar(50) ,
    sAddress varchar(100),
    ClassName varchar(100)
    )

    INSERT INTO @tblStudentDtl (sName,sAddress,ClassName)
    SELECT sName, sAddress,ClassName FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName like +‘s%’ ORDER BY sName

    SELECT * FROM @tblStudentDtl

I have never heard that called a virtual table. It's a table-valued variable or table variable. — John Saunders, Jan 13 '15 at 03:26
virtual table is everywhere : http://www.tipsntracks.com/311/create-temporary-virtual-table-or-a-records-set-with-sql-server-stored-procedure.html for example — Nathan, Jan 13 '15 at 03:27
Not quite everywhere: https://social.technet.microsoft.com/Search/en-US?query=%22virtual%20table%22&ac=4#refinementChanges=85&pageNumber=1&showMore=false — John Saunders, Jan 13 '15 at 03:31
1) Have you tried it? You posted the code so have you tried to execute that code from a user that has no write access to the current DB? and 2) you should be _extremely_ wary of taking any information from an article in which the author makes up random terminology. It shows that they don't really know what they are talking about. Some consideration can be given since English is clearly not the author's first language, but he says "With DECLARE statement no Temporary tables are created in tempdb" which isn't true. And you would be wise to never use the term "virtual table" in an interview ;-). — Solomon Rutzky, Jan 13 '15 at 04:08
To be fair to the author, either he is mis-applying misunderstanding of Oracle/PL-SQL which even Metalink does reference "virtual tables" in some documentation or the author is much more accustomed to SQLite which does have a Virtual Table construct (https://sqlite.org/vtab.html) — Jason W, Jan 13 '15 at 04:19
@JasonW that may be the source of the incorrect term, but it is still irrelevant. "Virtual table" is very much _not_ a term associated with SQL Server, which that article is very much about. And the article is also technically incorrect. It would not have taken that author a whole lot of time / effort to do a little research to verify his info. A simple Google search of "sql server virtual table" shows that it isn't valid terminology, outside of possibly referring Views and/or the _inserted_ and _deleted_ tables in a Trigger or OUTPUT clause. So no, I was not being unfair. — Solomon Rutzky, Jan 13 '15 at 04:43
You are absolutely correct, and honestly I think you are being totally fair to the author - even a tiny bit of research would have went a long way. He was obviously in over his head :) — Jason W, Jan 13 '15 at 04:45

score 1 · Answer 1 · answered Jan 13 '15 at 03:40

These "virtual tables" are just Table Variables, and it's not much different that declaring a variable in your T-SQL. You don't need any special permissions. The table data is not even stored in the database you are connected to - it is stored in tempdb, same as a temp table.

MSDN Docs on the table data type: http://msdn.microsoft.com/en-us/library/ms175010.aspx

Sql Virtual Table with Read Only Access: insert, delete , update?

1 Answers1