The Mystery of the SUSER_NAME() and SUSER_SNAME() functions

Introduction

SQL Server has two functions to return the name of the current login: SUSER_NAME() and SUSER_SNAME(). So, you might ask, why are there two functions that do the same thing?

Well, they are not completely alike. To figure out the difference, let's see them in action first.

A Simple SUSER_NAME() vs. SUSER_NAME() Example

First we need a few principals to play with:

Now we can log in as TestLogin1 and run this SELECT statement:

As expected, both functions return the exact same value:

The difference lies in the parameter. Both functions take a single optional parameter. If the parameter is supplied, the name of the server_principal that that parameter is pointing at is returned instead of the name of the current login. SUSER_NAME() takes the principal_id of the server principal, whereas SUSER_SNAME() takes the SID:

This statement calls both functions with their respective correct parameters. The result looks like this, independent of the account you used to login:

So, which function you need to use, depends on what type of information you have about the server principal whose name you want to look up.

A SUSER_NAME() Use Case

If you for example want to look up the name of the principal owning a server role, you have to use the SUSER_NAME() function because the information about the owner is provided as a principal id in the owning_principal_id column:

Because we specified AUTHORIZATION TestLogin1 when we created the role, TestRole1 is owned by TestLogin1 and that is what above query returns:

A SUSER_SNAME() Use Case

If you want to look up the name of the associated login for a given user, you can either join the sys.database_principals table to the sys.server_principals table as shown in SQL Server Database Users and the sys.database_principals catalog view, or you can call SUSER_SNAME() passing in the SID value from the sys.database_principals table like this:

The TestUser1 user was created from the login TestLogin1, so we expect that name to be returned. And it is:

Summary

SUSER_NAME() and SUSER_SNAME() both return the name of the current login when called without parameter. Their different strengths are revealed when they are called passing in a value identifying the server principal. The former takes the principal id as input whereas the latter takes the SID.

If you followed along with the examples, run the following statements to remove the test principals.