SELECT USER gives [guest] as response??

[kjv1611]

I was using the [blue]USER[/blue] function in a SQL string variable and it was working great.

Well, that was up until this morning... on one server.

As of this morning, when I run the exact same SQL, the USER function is now returning [guest] instead of my username.

Here's how I use the USER function:

Where I work, the username or connection is also used as the schema in our Sandbox databases (in general). So, my thought was to use the USER function to make it dynamic, not requiring any change, for whenever a different person has to run the same SQL.

Can anyone, first, tell me WHY it's suddenly returning "guest"? The connection shows the same settings as normal - shows what I would expect, but the USER function keeps returning guest...

I've asked a couple others with the same access on the same SQL Server, and theirs return correctly.

I've tried closing and reopening the SQL Server instance on my end (client) and I've tried even rebooting Windows... still no go.

Does anybody have a clue as to what could have changed for me to be pulling "guest" I realize there is a "guest" account, but that's supposed to be more of a system account, not the human users, right??

Thanks for any info.

 

[philhege]

Based on the definition of the USER function, for whatever reason, your connection to that instance is in the context of the guest user.

-------++NO CARRIER++-------

 

[kjv1611]

Yeah, it just seems really odd, b/c it's in any instance I setup on 2 SQL Servers (I had only noticed one earlier, but tested on all this afternoon).

I've mentioned it to a couple DBA/BI folks here at work, and a couple of folks are looking into it.

One guy mentioned that it could have been a result from a recent audit, b/c there was an audit done recently that may have triggered the wrong change(s), I suppose.

Most people I talked to had no clue, and hadn't even used the USER function to begin with.

 

[kjv1611]

I'll post back here with whatever I find out for the solution and/or reason, in case anyone else runs into the same issue.

 

[kjv1611]

Well, I've now got an answer, and actually, I thought it was pretty neat, and at the same time pretty overwhelming, considering all the ways to pull a UserName.

Apparently, since I can connect to multiple servers, it's to do with that. It's based on what server I'm connected to when I try to run any query against any certain server. So, if I first connect to the same server, and THEN run the script, it uses USER the way I expected. If I instead connect to a different server, and THEN come back and run the script against a server I was not previously connected to, it returns guest.

So, as an interesting test, one of the guys gave me this list of functions to run to compare:

SELECT USER, USER_NAME(), SYSTEM_USER, SUSER_SNAME()

And it returned...

guest guest DOMAIN\username DOMAIN\username

So, if anyone else sees this type of thing, don't panic, just use a different function.