April 11, 2016 at 2:13 pm
SQLRNNR (4/11/2016)
I am testing this on SQL Server 2014 and am not able to reproduce your findings. I get the expected query results with or without the GO or database context change.So....
Is your version SQL Server 2012? If so, I will commence testing there.
I am on a SQL 2014 instance (12.0.2000) and using SSMS 2014 to view the server in case that helps. The database in question is set up as Sql Server 2014 (120). To my knowledge there is nothing special in the creation of the DB, defaults were used.
Also, I am only seeing the error under the restricted test user account (and assuming anyone else utilizing the role). When I log onto the server as myself (and I am an admin), then the using and the go statements work without throwing an error.
As for now, I can only presume that maybe you are running into an issue where SSMS is confusing which user is actually performing the queries (as can be manifested by performing an execute as, changing database context, and then trying to revert permissions.
I am not trying to impersonate a user in my session. I have two SSMS open - one session I am logged in as myself and the other session is logged in as the test user, so there should not be any reverting permissions.
April 11, 2016 at 2:18 pm
Are there any tables or views outside the Reporting schema directly or indirectly referenced in the query that's failing? The error message referred to one Stats schema.
😎
April 11, 2016 at 2:46 pm
Eirikur Eiriksson (4/11/2016)
Are there any tables or views outside the Reporting schema directly or indirectly referenced in the query that's failing? The error message referred to one Stats schema.😎
I am pretty sure that the answers to your questions concerning how the DB tables reference each other were laid out in a previous post here: http://www.sqlservercentral.com/Forums/FindPost1774433.aspx
At a high level recap, yes the reporting schema does directly and indirectly references tables that are outside its schema. In this simple case, the view is directly referencing the base table (which is in the STATS schema), while the user has select only access to the Reporting schema - given through a role - (where the view is located). The user does not have access to the STATS schema.
When not using the USING and GO statements, the query executes perfectly and returns the expected results. When using only the USING statement, the query executes perfectly and returns the expected results. Only with the GO does the query throw the error - when logged in as the user. When logged in as myself, the query works fine in all instances.
April 11, 2016 at 2:52 pm
Is there a procedure named GO?
April 11, 2016 at 2:57 pm
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" 🙂
April 11, 2016 at 4:13 pm
Phil Parkin (4/11/2016)
ThisI 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.
Does this run OK?
select getdate()
go 3
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 11, 2016 at 11:07 pm
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.
April 12, 2016 at 5:19 am
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".
Can you post the results of the following please?
select ss.name schema_name, so.name object_name, so.type_desc
from sys.schemas ss
inner join sys.objects so on so.schema_id = ss.schema_id
where ss.name = 'Reporting'
and so.name = ' WorksheetReporting';
April 12, 2016 at 8:02 am
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".
Can you post the results of the following please?
select ss.name schema_name, so.name object_name, so.type_desc
from sys.schemas ss
inner join sys.objects so on so.schema_id = ss.schema_id
where ss.name = 'Reporting'
and so.name = ' WorksheetReporting';
Since the test user was given only access to 1 object, maybe it is a bug.
Under his own ID, does he have access to more than just the one view?
If so, I'd add another view and see if it goes away.
April 12, 2016 at 10:16 am
I feel like I'm grasping at straws, but we are about to that point.
If you check the user info right before the SELECT, do the results look the same both with and without the GO (when the query succeeds and errors out):
SELECT SYSTEM_USER, CURRENT_USER, SESSION_USER, USER, ORIGINAL_LOGIN()
I don't know why they would be different, but if they are that would be good to know.
If you sub in the table name where you have the view, do you still get the same results (works without the GO, fails with the GO). I'm guessing it will fail both times, but if it succeeds without the GO, again it would be good to know.
Chad
April 12, 2016 at 10:30 am
arbra.strong (4/11/2016)
SQLRNNR (4/11/2016)
I am testing this on SQL Server 2014 and am not able to reproduce your findings. I get the expected query results with or without the GO or database context change.So....
Is your version SQL Server 2012? If so, I will commence testing there.
I am on a SQL 2014 instance (12.0.2000) and using SSMS 2014 to view the server in case that helps. The database in question is set up as Sql Server 2014 (120). To my knowledge there is nothing special in the creation of the DB, defaults were used.
Also, I am only seeing the error under the restricted test user account (and assuming anyone else utilizing the role). When I log onto the server as myself (and I am an admin), then the using and the go statements work without throwing an error.
As for now, I can only presume that maybe you are running into an issue where SSMS is confusing which user is actually performing the queries (as can be manifested by performing an execute as, changing database context, and then trying to revert permissions.
I am not trying to impersonate a user in my session. I have two SSMS open - one session I am logged in as myself and the other session is logged in as the test user, so there should not be any reverting permissions.
Thanks for that update.
Recreating this scenario on my 2014 test instance I am unable to reproduce that error. The only time I end up with any errors is when I impersonate. Logging in directly with the account that is restricted, I can't get it to error on the "GO" as is happening for you.
I wonder if maybe some "hardening" has been attempted with the "public" role. I have seen really weird things happen when people try to harden the public role to make the server more secure.
Of course it is quite possible I have done something different in following the setup, to reproduce the issue, from what you have done, but I don't see what might be different - yet.
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
April 12, 2016 at 10:33 am
Chad Crawford (4/12/2016)
I feel like I'm grasping at straws, but we are about to that point.If you check the user info right before the SELECT, do the results look the same both with and without the GO (when the query succeeds and errors out):
SELECT SYSTEM_USER, CURRENT_USER, SESSION_USER, USER, ORIGINAL_LOGIN()I don't know why they would be different, but if they are that would be good to know.
If you sub in the table name where you have the view, do you still get the same results (works without the GO, fails with the GO). I'm guessing it will fail both times, but if it succeeds without the GO, again it would be good to know.
Chad
I was thinking the same thing during my initial attempts to repro this problem. I was thinking with the database context change it might be swapping the user because the user might not have the default database set to same database being worked in, or the user might have permissions in the default database that has been set.
But since I couldn't repro, and my context doesn't change at the go, I gave up on that theory.
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
April 12, 2016 at 11:17 am
Greg Edwards-268690 (4/12/2016)
Since the test user was given only access to 1 object, maybe it is a bug.
Under his own ID, does he have access to more than just the one view?
If so, I'd add another view and see if it goes away.
OK.....I am done.
Thanks to everyone for trying to find a solution.
The reason that I state this, is that the above quote got me thinking.....the user does not have access to only one view - they have access to approx. 20 views. They were given access to only 1 schema, but that schema does have multiple views attached to it.
So I went and tried some of the other views.....and they all work as expected with the using and the go statement. So then I went back to the view giving me an issue, rescripted it, and it failed again. Went to a different view and it worked. NOTE: other than a few additional fields, some of the views working are just like my test view, only selecting information from a single STATS schema table.
So since this is now not making any sense (not that it was before), and we are down to grasping at straws anyways, I am saying that this discussion is finished.
I will just file it away in case I start getting permission errors again to try to remove any above GO's and see if that helps.
Again, I am very grateful for everyone for trying to figure out this issue that has been driving me crazy....It would be my luck that in testing I would choose the ONLY view that does not work as expected.
April 12, 2016 at 11:44 am
arbra.strong (4/12/2016)
Greg Edwards-268690 (4/12/2016)
Since the test user was given only access to 1 object, maybe it is a bug.
Under his own ID, does he have access to more than just the one view?
If so, I'd add another view and see if it goes away.
OK.....I am done.
Thanks to everyone for trying to find a solution.
The reason that I state this, is that the above quote got me thinking.....the user does not have access to only one view - they have access to approx. 20 views. They were given access to only 1 schema, but that schema does have multiple views attached to it.
So I went and tried some of the other views.....and they all work as expected with the using and the go statement. So then I went back to the view giving me an issue, rescripted it, and it failed again. Went to a different view and it worked. NOTE: other than a few additional fields, some of the views working are just like my test view, only selecting information from a single STATS schema table.
So since this is now not making any sense (not that it was before), and we are down to grasping at straws anyways, I am saying that this discussion is finished.
I will just file it away in case I start getting permission errors again to try to remove any above GO's and see if that helps.
Again, I am very grateful for everyone for trying to figure out this issue that has been driving me crazy....It would be my luck that in testing I would choose the ONLY view that does not work as expected.
Well that really sucks that a root cause could not be found.
I am wondering if you could script the entire database and users to allow me to play with it some more so I can see what I missed. I would like to find an answer still.
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
April 12, 2016 at 4:28 pm
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
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply