This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 94%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
I think this question is closer to what's going on.
Last month I logged in to our sql server using SSMS and Windows authentication. Then I typed:
SELECT USER (the result would show 'dbo')
But this month:
SELECT USER (the result shows 'MyDomain\Myname')
What I'm after is getting things back to 'dbo'. Can that be done? If so, how?
Hi @Coreysan,
SYSTEM_USER: returns Server Login name that was used to login to the instance (either SQL Server login or AD/Domain/Windows user-name).USER, CURRENT_USER, USER_NAME(), or SESSION_USER: these all return Database User principal, which is (by default) dbo if you are db-owner or logged in as a sysadmin or sa.Please have a double check of your code.
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.
Thanks so much for your response. I performed both queries, and here are the results:
SUSER_NAME() = 'MyDoman\MyName'
USER = 'MyDomain\MyName'
I'd like to ask my DBA to work his magic so that USER reverts to "dbo". What can I recommend he do? I'm not sure that he knows, any more than I know what to do!
The system function "USER" returns the "database username". One SQL Server login can have different database username on different database. Are you connected to the same database as before? I guess not.
See
https://learn.microsoft.com/en-us/sql/t-sql/functions/user-transact-sql?view=sql-server-ver16
SELECT USER would return dbo under one of these two conditions:
I hope this information can help you to deduce what might have changed. You may have to talk to your DBA to have things changed back.
Thanks for responding.
(I'm in dialog with my DBA. I'm also interested in learning it myself, so I appreciate your time.)
Years ago I created the database. Then the owner was changed 2 years ago. Finally, my permissions
were changed for all our databases about 1 year ago. (I'm no longer in admin)
I can tell you that I have a DB level user account, and I can view the information in:
myDB/Security/Users/MyName/Properties/General. The default schema is "MyDomain/MyName".
What I find curious is that I checked another server and database that works properly, but there's no DB level user account set up for me.
Do you have any other thoughts? (If it gets too complex, I'll definitely ask my DBA to figure it out.)
Obviously, I cannot why things are as they are at your site.
Note that you don't have to be a user in the database to get access; you may get access through membership in an AD group. (In fact, this is the normal way to get access.)
By the way, you should be able to change your default schema with the ALTER USER command:
ALTER USER "Mydomain\Myuser" WITH DEFAULT_SCHEMA = dbo