Thursday, September 25, 2008
Working with Users
10:36 | 
Posted by
Sean McCown
Ok, so I don't rant in here nearly as much as I could or probably even should, but here's a good one for you.
I got a call from a user who said that he was getting different results when he ran an SP under 2 different user accts. There were no errors, just one was returning results and the other wasn't.
So I got on and ran the SP under his acct and the other acct and I couldn't see any differences. So I looked at the code real quick and didn't see anything weird and then I wrote him to say that nothing was wrong. He insisted that there was still a problem so I deferred troubleshooting to this morning. I got in today and pulled the most recent copy of the SP and started looking at the code. I still didn't see anything that would warrant the different results. I got pulled away for about 30mins and when I came back I got him on chat and we started going through it. I ran the SP under his acct and got an error. Weird... Then I ran it under the other acct and got the same error... Not really that weird considering that I'm looking for different behavior and didn't expect to find it. So now I've at least got something to troubleshoot.
The error said that #table didn't exist, but I could see it being created with a select into. I stepped through the different lines of the SP and it created the #table just fine and returned results with no problem. But when I ran it, it errored out again. What the hell could be causing this thing to error out when it was called as an SP, but not when it's called as adhoc SQL? Then it hit me... and I was right! He had gone in and changed the SP and taken out the select into line while I had stepped away for that 30mins. I was working with old code.
It's a shame that things that start out so promising end with such a fizzle. I was hoping to dig into some weird bug that only happened on thurs mornings. Anyway, I'm still not seeing any different behavior and I suspect he's going to find that he's doing something else different.
Oh, and the method I was using to test the different user accts?
execute as login = 'LoginName' --set to the user you want to test.
SELECT SUSER_NAME(), USER_NAME(); --ensure you're working under the right user.
run your code here...
REVERT -- now set it back to you. It's that easy.
I got a call from a user who said that he was getting different results when he ran an SP under 2 different user accts. There were no errors, just one was returning results and the other wasn't.
So I got on and ran the SP under his acct and the other acct and I couldn't see any differences. So I looked at the code real quick and didn't see anything weird and then I wrote him to say that nothing was wrong. He insisted that there was still a problem so I deferred troubleshooting to this morning. I got in today and pulled the most recent copy of the SP and started looking at the code. I still didn't see anything that would warrant the different results. I got pulled away for about 30mins and when I came back I got him on chat and we started going through it. I ran the SP under his acct and got an error. Weird... Then I ran it under the other acct and got the same error... Not really that weird considering that I'm looking for different behavior and didn't expect to find it. So now I've at least got something to troubleshoot.
The error said that #table didn't exist, but I could see it being created with a select into. I stepped through the different lines of the SP and it created the #table just fine and returned results with no problem. But when I ran it, it errored out again. What the hell could be causing this thing to error out when it was called as an SP, but not when it's called as adhoc SQL? Then it hit me... and I was right! He had gone in and changed the SP and taken out the select into line while I had stepped away for that 30mins. I was working with old code.
It's a shame that things that start out so promising end with such a fizzle. I was hoping to dig into some weird bug that only happened on thurs mornings. Anyway, I'm still not seeing any different behavior and I suspect he's going to find that he's doing something else different.
Oh, and the method I was using to test the different user accts?
execute as login = 'LoginName' --set to the user you want to test.
SELECT SUSER_NAME(), USER_NAME(); --ensure you're working under the right user.
run your code here...
REVERT -- now set it back to you. It's that easy.
Subscribe to:
Posts (Atom)
About Me
- Sean McCown
- I am a Contributing Editor for InfoWorld Magazine, and a frequent contributor to SQLServerCentral.com as well as SSWUG.org. I live with my wife and 3 kids, and have practiced and taught Kenpo for 22yrs now.
Page Views
