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

2014-01-11 - Fundamentals, General, Security

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:

[sql] CREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE USER TestUser1 FOR LOGIN TestLogin1;
CREATE SERVER ROLE TestRole1 AUTHORIZATION TestLogin1;
[/sql]

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

[sql] SELECT SUSER_NAME() AS [SUSER_NAME()], SUSER_SNAME() AS [SUSER_SNAME()];
[/sql]

As expected, both functions return the exact same value:

SUSER_NAME() vs SUSER_SNAME()

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:

[sql] SELECT SP.name,
SUSER_NAME(SP.principal_id) AS [SUSER_NAME(SP.principal_id)],
SUSER_SNAME(SP.sid) AS [SUSER_SNAME(SP.sid)] FROM sys.server_principals AS SP
WHERE SP.name = 'TestLogin1';
[/sql]

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

SUSER_NAME() and SUSER_SNAME() with Parameters

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:

[sql] SELECT SP.name,
SP.owning_principal_id,
SUSER_NAME(SP.owning_principal_id) AS owning_principal_name
FROM sys.server_principals AS SP
WHERE SP.name = 'TestRole1';
[/sql]

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

SUSER_NAME() in Action

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:

[sql] SELECT DP.name,
DP.SID,
SUSER_SNAME(DP.sid) AS [SUSER_SNAME(DP.sid)] FROM sys.database_principals AS DP
WHERE DP.name = 'TestUser1';
[/sql]

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

SUSER_SNAME() in Action

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.

[sql] DROP SERVER ROLE TestRole1;
DROP USER TestUser1;
DROP LOGIN TestLogin1;
[/sql]

Categories: Fundamentals, General, Security
Tags: , , , , ,

One Response to The Mystery of the SUSER_NAME() and SUSER_SNAME() functions

  1. Pingback: Blogger questions: What if someone else wrote about the same subject? | SQL Studies

Leave a Reply