Security - View is not able to select from base table

Question

Security - View is not able to select from base table

Viewing 2 posts - 31 through 31 (of 31 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

SQLBill (4/12/2016)
Before you completely quit...try one more thing. Run a trace. Maybe that will show you what is really happening. Since this is so weird, make sure you capture all the columns available and try different events. There is one column (SessionLoginName) that you should also make sure you are capturing.
-SQLBill

Along the trace lines, here is an Extended Event Session that could prove quite useful.

http://bit.ly/1qPHQTF

I use the article to show how I just used that same session to figure out another issue. Since you are running 2014, this should make a lot of sense to use.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 2

Phil Parkin (4/11/2016)
This
I am getting the tilde under the 3 in the editor and it states that "Could not find the stored procedure", but ...
Is odd.
I don't know what is happening, but you should not see that.

and

Ed Wagner (4/12/2016)
Lynn Pettis (4/11/2016)
arbra.strong (4/11/2016)
Lynn Pettis (4/11/2016)
Is there a procedure named GO?
No....I am able to put GO after the select statement and it runs with no errors. It is only when it is placed above the select that it causes issues.
Also, as a side note, if I had found a stored procedure named that, I would soon be on the news for "reasons" 🙂
Just checking.
Arba, I think this is along the same lines as me asking about the batch separator. The problem is so weird that I think we're all looking for the less-sane type of things. It just doesn't make sense. I even looked closely at your "GO" to make sure it was "GO" and not "G0".

Given that the mere existence of the "GO" makes a difference, and SQL Server knows nothing of the "GO", I would think that the focus should be on SSMS.

Regarding the "GO" possibly being "G0" or the name of a Stored Procedure: those are not possibilities since either one would have resulted in the "GO" not highlighting in blue and being a visual indication that something was wrong. And if it was "G0" (G and a zero) then it would get a "Procedure G0 not found error" when executing.

Regarding the red underline below the "3" in the "GO 3", that is an issue with SSMS. I get the same running SSMS 2014, including the hover text of "cannot find procedure ''. ". Interestingly enough, turning on "SQLCMD Mode" (in the "Query" menu) gets rid of the red underline.

But I think the red underline on the "3" is a clue. SSMS is doing something, something that goes away with SQLCMD Mode. On a hunch, I turned off IntelliSense and the red underline again disappeared. What if the permission issue isn't related to executing the query, but related to IntelliSense trying to do meta-data discovery (i.e. "SET FMTONLY ON" / "sp_describe_first_result_set"? ). I think the next thing to test (and which is really easy to do) is run the query that errors again, and upon getting the error, try both turning SQLCMD Mode to ON (in the Query menu), and turning it off again and then turning IntelliSense OFF / Disabled (also in the Query menu). You could also try running a short script via SQLCMD.EXE to see if that has the problem (I am guessing that it wouldn't show up there).

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR