July 22, 2009 at 8:27 am
Hi,
See if you can figure this out, because I cannot.
I have full rights as dba on a database. I have created a user who has is a member of the db_datareader role and also has explicit execute permissions on a stored procedure (user dwaccess).
Now, when I run that stored proc as user dwaccess, no data is returned. It is a simple select statement, 3 parameters and one subquery that uses the paramaters.
Running as me, it returns data.
If I now take that select query and declare the 3 parameters instead as variables so I can run the select and see what is happening, I get the following (ie. connecting in MStudio as dwaccess):
1) data is returned if I comment out the where clause in the subquery that uses those parameters.
2) data is not returned if I leave the where clause in the subquery
What on earth is going on? Why such different behaviour?
Any comments/suggestions desperately and gratefully received,
Thanks, 😀
Richard
July 22, 2009 at 8:32 am
You aren't holding your tongue in the right position. :w00t:
Really, you ask a question expecting an answer and don't even bother to show us anyhting?? Please read the first article I reference below in my signature block about asking for assistance.
July 22, 2009 at 9:39 am
Are there functions in the WHERE clause?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 22, 2009 at 1:47 pm
You might be returning NULL values in your WHERE clause which can make comparisons fail flat out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 22, 2009 at 4:55 pm
Are the tables in your sub-queries schema qualified? If not, is it possible that user has one or more of those objects in it's own schema that does not have any data?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2009 at 12:21 am
"Really, you ask a question expecting an answer and don't even bother to show us anyhting?? " :w00t:
Sorry, but I believe the actual code is irrelevent to the problem., which is why I did not show you anything. 😉
I took great care in describing the problem accurately regarding the behaviour and believe the code - a simple select, a simple subquery, a simple where clause - is not necessary to see; the actual code is not the issue, it's the behaviour I want people to focus on....ie. what could cause this behaviour? :hehe:
I believe I have found the answer and would welcome confirmation from others:
While trying to debug what was happening I tried different security settings for the user. Do I have to restart the Query session in order to pick up those security changes? (ie. I assume that the MStudio query tool does not automatically 'refresh' the user profile I am using).
Thanks,
Richard
July 23, 2009 at 5:35 am
The code is almost always relevant to the problem when we're talking about TSQL.
As several people have pointed out, depending on what was referenced or how things were referenced in your TSQL code, you may have seen the behavior in question. But most of us didn't propose answers because we're not sitting in your seat. We can't see what you can see. The more facts, not context, you provide, the easier it is to try to help out.
To answer the last question, yes, if you change security settings you need to drop the connection and reconnect.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 23, 2009 at 6:47 am
Richard McSharry (7/23/2009)
"Really, you ask a question expecting an answer and don't even bother to show us anyhting?? " :w00t:Sorry, but I believe the actual code is irrelevent to the problem., which is why I did not show you anything. 😉
I took great care in describing the problem accurately regarding the behaviour and believe the code - a simple select, a simple subquery, a simple where clause - is not necessary to see; the actual code is not the issue, it's the behaviour I want people to focus on....ie. what could cause this behaviour? :hehe:
I believe I have found the answer and would welcome confirmation from others:
While trying to debug what was happening I tried different security settings for the user. Do I have to restart the Query session in order to pick up those security changes? (ie. I assume that the MStudio query tool does not automatically 'refresh' the user profile I am using).
Thanks,
Richard
Confirmation on what? Again, you haven't even told us what you may have found that was causing the problem.
And I second Grant regarding everything he said in response to your post quoted above.
July 23, 2009 at 9:36 am
Sorry, but I did point out what I had found and what I thought the problem was:
"While trying to debug what was happening I tried different security settings for the user. Do I have to restart the Query session in order to pick up those security changes?"
That is what I believe the problem to be; that is what I believe was causing the strange behaviour.
Thank you Grant for confirming this. :w00t:
I understand that 99% of the time it helps to see the code. I could have posted this:
SELECT
sT1.OldName,
T1.NewName
FROM
(SELECT
Key,
OldName
FROM
tblOldNames
WHERE
OldName = @pName
) as sT1
LEFT JOIN
tblNames as T1
ON
stT1.Key - T1.Key
which is what the query is doing.
I believe I described that query quite adequately in the question; I don't see that having the code spelled out in this particular instance helps solve the problem. Maybe I'm wrong and if so, happy to admit it but I wanted to avoid questions like "have you checked the datatype of the parameter" and "have you checked permissions on tblName" because I knew those were not the issues.
But thank you for replying and sorry to have wasted your time.
July 23, 2009 at 9:53 am
I honestly don't think your problem is necessarily related to security - at least, not directly. If a user does not have access to an object, you are going to get a run-time error telling you the user does not have SELECT permissions on that object.
One of the things you should know is that it is recommended as a best practice, especially in SQL Server 2005 and greater to schema qualify all objects. Objects are no longer owned by a user, they are owned by a schema. If you don't schema qualify the object - the first place the system looks is in the users default schema.
If you don't set the users default schema to dbo - then you can get some unusual results if the user has an object with the same name in their default schema.
So, yeah - showing the code can be important and showing an attitude when asked to include it does not help you get the help you are looking for.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2009 at 10:26 am
Here comes one of those "awful" questions you didn't want to get.... Are you SURE a value is being passed through for the parameter (when you're firing this from within the app)? I've seen minor change made in a ASP.NET project screw up the parameter mapping/population process, and then poof - no data.
What's worse - if that happens, it may not be bothering to fire the data query at all.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 24, 2009 at 3:13 am
Thanks you for your input re. the schemas, I was aware of the need to use fully qualified names, but not sure exactly of the reason why.
Sorry if you think I am displaying an attitude. I have tried very hard to explain the problem and in replying without causing offence to anyone. I'm not stupid enough to insult the people I am asking help for.
Sorry if my English comes across as having an attitude, that is not/was not my intention at all. I was simply trying to explain my reasoning. Clearly its flawed, or I am an idiot, as the post title says.
The fact that recreating the session resulted in correct data being returned indicates that a security setting I had changed was the problem.
July 24, 2009 at 7:13 am
General observation:
Keep in mind Richard that since you have figured out how to make it work, and you see all of the moving parts along the way; you may feel that you have explained adequately.
However, sometimes it helps to see the code to really understand the problem, because the way you stated the problem may not be as crystal clear as you think. (sometimes I'm an idiot when I read things, and just plain get confused)
plus, you may get bonus suggestions to make it run better that you weren't even asking for, especially with this crowd.
Glad you got it figured out, hope you'll come back and ask next time you need something else!
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 24, 2009 at 9:19 am
Matt Miller (7/23/2009)
Here comes one of those "awful" questions you didn't want to get.... Are you SURE a value is being passed through for the parameter (when you're firing this from within the app)? I've seen minor change made in a ASP.NET project screw up the parameter mapping/population process, and then poof - no data.What's worse - if that happens, it may not be bothering to fire the data query at all.
Thanks for the suggestion, but that was the first thing I tested. Hence why I then used MS Studio to impersonate the user and ran the raw code using declared variables and the sql, bypassing the proc completely. It was the behaviour at this point that caused my post.
But thanks for taking the time to post.
July 24, 2009 at 9:23 am
jcrawf02 (7/24/2009)
General observation:Keep in mind Richard that since you have figured out how to make it work, and you see all of the moving parts along the way; you may feel that you have explained adequately.
However, sometimes it helps to see the code to really understand the problem, because the way you stated the problem may not be as crystal clear as you think. (sometimes I'm an idiot when I read things, and just plain get confused)
plus, you may get bonus suggestions to make it run better that you weren't even asking for, especially with this crowd.
Glad you got it figured out, hope you'll come back and ask next time you need something else!
Jon
Thanks for the observation, which is very valid.
I visit the site whenever I can, which is not as often as I like, but when I post a qustion in future I will be sure to post code....if only to avoid getting pointed at. :hehe:
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply