SUSeR_name vs SESSION_USER VS OTHERS. I need de login name

Question

I created a new login with many roles called "administrator1"
I used to connect in SSMS login administrator1 but when I try to know its login name(administrator1) exactly, I see dbo or sa, but I need to see administrator1.
I tried ORIGINAL_LOGIN( ), CURRENT_USER, SUSER_NAME(), USER_NAME(), SYSTEM_USER and don't work. Somebody can help me please. Thanks. Miguel

Answer 1

Was that a Windows login or an SQL login?

There are a number of functions to return the "current user". They fall in the to three groups:

• CURRENT_USER, USER, user_name(), database_principal_id() and SESSION_USER. They all return the current user on database level. All but database_principal_id() return the name. database_principal_id() returns an id.
• SYSTEM_USER, suser_name, suser_sname(), suser_id(), suser_sid(). They return the information about the current user on server level, in SQL Server parlance known as the login. The first three return the same name, the fourth return a numeric id and the last a SID which is a long binary value.
• Original_login(). This function returns the name of the login that actually logged into SQL Server. It can be different from SYSTEM_USER & co, if there is impersonation in play.

Mapping between server logins and database users are by SID, not by name. Furthermore, if you are the database owner or you are member of the sysadmin role, you will always map to the user dbo on database level. This can explain why the user function returns dbo.

If the server-level functions, including original_login() returns sa, my interpretation is that you logged in as sa and not administrator1.

Answer 2

Hi @Miguel Riano Abril ,

Welcome to Microsoft Q&A!
Please refer to this related article: https://database.guide/return-the-current-login-name-in-sql-server-tsql/
And this: https://stackoverflow.com/questions/4101863/sql-server-current-user-name
Hope these could give you some ideas.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.