Understanding different ways to extract Logged In User/Login in SQL Server using TSQL

In SQL Server there are different ways to get Logged in user and login using TSQL. Most frequently used options are narrated as below –

SELECT ORIGINAL_LOGIN();
SELECT SUSER_NAME(SUSER_ID());
SELECT SUSER_SNAME(SUSER_SID());
SELECT SYSTEM_USER;
SELECT CURRENT_USER;
SELECT USER_NAME(USER_ID());
SELECT SESSION_USER;
SELECT USER;


Lets visit one by one of them –

ORIGINAL_LOGIN();

returns the name of the original identity of very first login that connected to the instance of SQL Server. Even in case we use EXECUTE AS with a separate login, ORIGINAL_LOGIN() does return the original login.

SELECT ORIGINAL_LOGIN() OriginalLogin
GO

CREATE LOGIN SampleLogin WITH PASSWORD = 'P@ssw0rd';
CREATE USER SampleUser FOR LOGIN SampleLogin;
GO

EXECUTE AS LOGIN = 'SampleLogin'
GO
SELECT ORIGINAL_LOGIN() OriginalLogin
GO

REVERT
GO

SELECT ORIGINAL_LOGIN() OriginalLogin
GO

DROP LOGIN SampleLogin;
DROP USER SampleUser;
GO


UserLogins1

SUSER_NAME(SUSER_ID());
SUSER_SNAME(SUSER_SID());

When Executed without any parameters, these two system defined functions does return the same value of the Login to the SQL Server of that particular context. I mean in case if we use EXECUTE AS, then both the functions will result in same new context values. But things get interesting when we call these functions with parameters, SUSER_SNAME() will be taking SID of the login and SUSER_NAME() will be taking ID if the login. So now we have to narrow down the difference between SID and ID.

As per MSDN – "When a SQL Server login is created, it is assigned an ID and a SID. These are visible in the sys.server_principals catalog view as principal_id and SID. The ID (principal_id) identifies the login as a securable within the server. It is assigned by SQL Server when the login is created. When a login is deleted, its ID number is recycled. The SID identifies the security context of the login and is unique within the server instance. The source of the SID depends on how the login is created. If the login is created from a Windows user or group, it is given the Windows SID of the source principal; the Windows SID is unique within the domain. If the SQL Server login is created from a certificate or asymmetric key, it is assigned a SID derived from the SHA-1 hash of the public key. If the login is created as a legacy-style SQL Server login that requires a password, the server will generate a SID."

Till now in my routines, I never found a requirement to use both the functions with parameters. I am counting for a chance to be able to use these functions with parameters.

Try this part of code –

SELECT 
    SUSER_NAME() AS Name1,
    SUSER_NAME(1) AS Name2,
    SUSER_SNAME() AS Name3,
    SUSER_SNAME(Give a valid SID from sys.server_principals table) AS Name4

And output should be something like this –

UserLogins2

 

SELECT SUSER_NAME() AS Name1, SUSER_SNAME() AS Name2
GO

CREATE LOGIN SampleLogin WITH PASSWORD = 'P@ssw0rd';
CREATE USER SampleUser FOR LOGIN SampleLogin;
GO

EXECUTE AS LOGIN = 'SampleLogin'
GO
SELECT SUSER_NAME() AS Name1, SUSER_SNAME() AS Name2
GO

REVERT
GO

SELECT SUSER_NAME() AS Name1, SUSER_SNAME() AS Name2
GO

DROP LOGIN SampleLogin;
DROP USER SampleUser;
GO

 

UserLogins3

 

SYSTEM_USER;

SYSTEM_USER is implemented as a synonym for SUSER_SNAME() (absolutely similar to SUSER_SNAME specified without a security_identifier).

USER_NAME(USER_ID());

When executed without any parameters, this function does return the same value of the user of database in that particular context. I mean in case if we use EXECUTE AS, it will return new User in that context. But things get interesting when we call USER_NAME() function with parameters, USER_NAME() will be taking ID of the user and it will return the database User.

As per MSDN – "When a database user is created, it is assigned an ID and a security ID (SID). These numbers are visible in the sys.database_principals catalog view as principal_id and SID. The ID identifies the user as a securable within the database. When a database user is deleted, its ID is recycled. The SID assigned to a database user is unique within the database. The source of the SID depends on how the database user is created. If the user is created from a SQL Server login, it is given the SID of the login. If the user is created from a certificate or asymmetric key, the SID is derived from the SHA-1 hash of the public key."

Till now in my routines, I never found a requirement to use this functions with parameters. I am counting for a chance to be able to use these functions with parameters.

PIC – With and with out parameter, and then with Execute As Example

SELECT USER_NAME() AS Name1, USER_NAME(2) AS Name2;

 

UserLogins4

 

SELECT USER_NAME() AS Name1
GO

CREATE LOGIN SampleLogin WITH PASSWORD = 'P@ssw0rd';
CREATE USER SampleUser FOR LOGIN SampleLogin;
GO

EXECUTE AS LOGIN = 'SampleLogin'
GO
SELECT USER_NAME() AS Name1
GO

REVERT
GO

SELECT USER_NAME() AS Name1
GO

DROP LOGIN SampleLogin;
DROP USER SampleUser;
GO

UserLogins5

 

CURRENT_USER;
SESSION_USER;
USER;

All these functions are same and return the same result of a Database User. They work the same as USER_NAME() without any parameters and can be considered as synonym for the same.

As per MSDN – "Either the ISO CURRENT_USER or SESSION_USER functions to obtain the database user name associated with the current connection. In Transact-SQL, these functions are implemented as synonyms for USER_NAME(). (USER_NAME specified without a database_user_ID parameter.) The Transact-SQL function USER is also implemented as a synonym for USER_NAME().

ISO allows for SQL statements to be coded in SQL modules that can have authorization identifiers separate from the authorization identifier of the user that has connected to an SQL database. ISO specifies that SESSION_USER always return the authorization identifier of the user that made the connection. CURRENT_USER returns the authorization identifier of the SQL module for any statements executed from an SQL module, or of the user that made the connection if the SQL statements were not executed from an SQL module. If the SQL module does not have a separate authorization identifier, ISO specifies that CURRENT_USER return the same value as SESSION_USER. SQL Server does not have separate authorization identifiers for SQL modules; therefore, CURRENT_USER and SESSION_USER are always the same. The USER function is defined by ISO as a backward compatibility function for applications written to earlier versions of the standard. It is specified to return the same value as CURRENT_USER."

SELECT CURRENT_USER AS Name1;
SELECT SESSION_USER AS Name2;
SELECT USER AS Name3;


UserLogins6

 

Last but not least, I need to thank Pinal Dev (SQL Server Evangelist & MVP), who motivated me through some of his blog posts on this particular topic which I narrated above. Also MSDN Articles greatly helped me to consolidate information.

 

Disclaimer:

Of late I am working most of my time in SQL Environment as a Data Architect (even though I prefer most of the time to work on ASP.Net Web stack, due to some unprecedented work challenges :-) I took Project Manager responsibilities. As a PM most of my work goes out through Excel and Outlook, but I don’t want to get away from being a developer. So I immediately opted for being a SQL Dev along with PM, alongside I code for some C# components for my dev team). I am excited working with SQL Server, especially with TSQL. So I started out rolling on very basic concepts on “HOW DO I” and wanted to share some of my routine findings with all my readers. As a result “SQL Server” tag landed up in my tag Cloud.

Most of the tutorials under this tag are going to be very basic (my learning experiences and code). As time moves on I would be shedding some complex stored procedures and data models of my interest. In last 3 months of time, I came across very valuable topics in Data world with some very famous tools. My future articles are going to cover some of them with very specific examples.

I hereby convey to all my readers, that most of the tutorials in this particular tag are going to be very quick, short and basic. They are outcomes of my day to day learning. There can be many ways to solve the problems in any technology, based on the that I would always try to advocate mostly best practices and performance oriented solutions. Please use some of the SQL which I will be narrating with care in your personal/work purposes.

You may also like...

2 Pingbacks/Trackbacks