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 :)
In SQL Server version 7.0, the security identification number (SID) replaced the server user identification number (SUID). SUSER_NAME returns a login name only for a login that has an entry in the syslogins system table. SUSER_NAME can be used in a select list, in a WHERE clause, and anywhere an expression is allowed.
suser_sname returns the login name associated with a security identification number (SID). When called without the optional parameter, it returns the name of the current security context.
If you call the function without an argument they will both return the same value. But they do take different arguments:
varbinary(85) SID
of a login as argumentinteger principal_id
of a loginYou 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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With