question

MiguelRianoAbril avatar image
0 Votes"
MiguelRianoAbril asked SeeyaXi-msft answered

SUSeR_name vs SESSION_USER VS OTHERS. I need de login name

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
1 Vote"
SeeyaXi-msft answered

Hi @MiguelRianoAbril,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.