T-SQL: SUSER_SNAME vs SUSER_NAME?

问题:

The MSDN documentation says for SUSER_SNAME function:

Returns the login identification name from a user's security identification number (SID).

More over, it says for the SUSER_NAME function:

Returns the login identification name of the user.

Nonetheless, when I execute the following SQL statements I get the same result:

SELECT SUSER_NAME();
SELECT SUSER_SNAME();

So, what are differences, and which one shall I use? Is there a situation I should use one rather that the other?

Please advice,

Thanks in advance :)

回答1:

If you call the function without an argument they will both return the same value. But they do take different arguments:

• SUSER_SNAME() takes the varbinary(85) SID of a login as argument
• SUSER_NAME() takes the integer principal_id of a login

You can verify this like:

select  suser_name(principal_id)
,       suser_name(sid)
,       suser_sname(principal_id)
,       suser_sname(sid)
from    sys.server_principals 
where   name = suser_name()

Only the first and last column will return non-null values.

回答2:

SUSER_NAME() will return the name associated with an sid that exists in sys.server_principals. The sid must exist in sys.server_principals.

SUSER_SNAME() can do that but also can return the sid of a login if the login is a member of an active directory group

So if you have [CONTOSO\MyGroup] in Active Directory and that group has one user [CONTOSO\MyUser]

And you add that group to SQL Server: CREATE LOGIN [CONTOSO\MyGroup] FROM WINDOWS;

SELECT SUSER_ID('CONTOSO\MyUser'), SUSER_SID('CONTOSO\MyUser')

will give you NULL, CONTOSO\MyUser because CONTOSO\MyUser is not in sys.server_principals but is in A/D