HowTo get Microsoft Windows user or group SID in MSSQL

Microsoft Windows user or group can have different names in different languages. this is why a SID is used.

SQL Server 2005 Books Online (September 2007)
SUSER_SNAME (Transact-SQL)

Returns the login name associated with a security identification number (SID).

Syntax

SUSER_SNAME ( [ server_user_sid ] )
Arguments

server_user_sid
Is
the login security identification number. server_user_sid, which is
optional, is varbinary(85). server_user_sid can be the security
identification number of any SQL Server login or Microsoft Windows user
or group. If server_user_sid is not specified, information about the
current user is returned.

Return Types

nvarchar(128)

Remarks

SUSER_SNAME
can be used as a DEFAULT constraint in either ALTER TABLE or CREATE
TABLE. SUSER_SNAME can be used in a select list, in a WHERE clause, and
anywhere an expression is allowed. SUSER_SNAME must always be followed
by parentheses, even if no parameter is specified.

When called
without an argument, SUSER_SNAME returns the name of the current
security context. When called without an argument within a batch that
has switched context by using EXECUTE AS, SUSER_SNAME returns the name
of the impersonated context. When called from an impersonated context,
ORIGINAL_LOGIN returns the name of the original context.

Examples

A. Using SUSER_SNAME
The following example returns the login name for the security identification number with a value of 0x01.

Copy Code
SELECT SUSER_SNAME(0x01);
GO
B. Using SUSER_SNAME with a Windows user security ID
The following example returns the login name associated with a Windows security identification number.

Copy Code
SELECT SUSER_SNAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000);
GO
C. Using SUSER_SNAME as a DEFAULT constraint
The following example uses SUSER_SNAME as a DEFAULT constraint in a CREATE TABLE statement.

Copy Code
USE AdventureWorks;
GO
CREATE TABLE sname_example
(
login_sname sysname DEFAULT SUSER_SNAME(),
employee_id uniqueidentifier DEFAULT NEWID(),
login_date datetime DEFAULT GETDATE()
)
GO
INSERT sname_example DEFAULT VALUES
GO
D. Calling SUSER_SNAME in combination with EXECUTE AS
This example shows the behavior of SUSER_SNAME when called from an impersonated context.

SELECT SUSER_SNAME();

GO

EXECUTE AS LOGIN = 'WanidaBenShoof';

SELECT SUSER_SNAME();

REVERT;

GO

SELECT SUSER_SNAME();

GO

Here is the result.

sa

WanidaBenShoof

sa

---------------------------------
To get the SID I use

sp_helplogins -shows information for all sql server users, incl. sid
sp_help -shows all buildin funktions (sp, tables ...)