SQL Server: Insert Result of WHOAMI into Table

Question

I have a table with 2 columns, one of which I would like to insert the result of the WHOAMI command. Tried doing the following but I presume it won't work due to the different amount of columns:

UPDATE tblName 
SET colName = (xp_cmdshell 'whoami.exe') 
WHERE id = 1

Answer 1

If you want the user who's logged into the database (which could be a Windows or a SQL Login), you could use the built in SUSER_NAME() function instead

UPDATE tblName set colName = SUSER_NAME() WHERE id=1

If you truly want the service account that's running the SQL Server process (which is what you'd get from whoami.exe), you may be better off using the sys.dm_server_services view, which won't require enabling xp_cmdshell (which can pose some security risks if it's not locked down properly) (note: this requires SQL Server 2008R2 SP1 at a minimum):

UPDATE tblName 
SET colName = (
    SELECT TOP 1 
       service_account 
    FROM sys.dm_server_services 
    WHERE servicename LIKE 'SQL Server (%') 
WHERE id=1

otherwise

DECLARE @output TABLE (v varchar(200))

INSERT @output (v) exec xp_cmdshell 'whoami.exe'

UPDATE tblName SET colName = (select top 1 * from @output) WHERE id=1