SUSER_SNAME or SUSER_NAME in audit column (Azure SQL db)

• SQL!$@w$0ME

  SSChampion

  Points: 12349

  More actions

  February 29, 2016 at 9:20 am

  #308432

  Hi All,

  I would like to audit the update user name in an audit column in a table which is in Azure SQL db. This is using update trigger. What is the preferred function to implement, SUSER_SNAME or SUSER_NAME or CURRENT_USER?

  Many thanks!

• Lowell

  SSC Guru

  Points: 323522

  More actions

  February 29, 2016 at 10:02 am

  #1861859

  for a lot of my trigger or procedure based audits, i don't limit myself; i just log everything, since you never know what might be relevant later.

  I add columns for all of the following:

  for 2008 and above:

  --the auditing snippet below works fine in a

  --login trigger,

  --database trigger

  --or any stored procedure.

  SELECT

  getdate() AS EventDate,

  DB_NAME() AS DBName,

  CURRENT_USER AS CurrentUser,

  HOST_NAME() AS HostName,

  APP_NAME() AS ApplicationName,

  OBJECT_NAME(@@PROCID) AS ProcedureName,

  USER_ID() AS Userid,

  USER_NAME() AS UserName,

  SUSER_ID() AS sUserid,

  SUSER_SNAME() AS sUserName,

  IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

  IS_MEMBER('db_owner') AS [Is_DB_owner],

  IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

  IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

  ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

  ConnectionProperty('net_transport') AS 'net_transport',

  ConnectionProperty('protocol_type') AS 'protocol_type',

  ConnectionProperty('auth_scheme') AS 'auth_scheme',

  ConnectionProperty('local_net_address') AS 'local_net_address',

  ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

  ConnectionProperty('client_net_address') AS 'client_net_address',

  ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQL!$@w$0ME

  SSChampion

  Points: 12349

  More actions

  February 29, 2016 at 10:08 am

  #1861862

  Thanks Lowell!

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  February 29, 2016 at 10:55 am

  #1861877

  Lowell (2/29/2016)

  ---

  for a lot of my trigger or procedure based audits, i don't limit myself; i just log everything, since you never know what might be relevant later.

  I add columns for all of the following:

  for 2008 and above:

  --the auditing snippet below works fine in a

  --login trigger,

  --database trigger

  --or any stored procedure.

  SELECT

  getdate() AS EventDate,

  DB_NAME() AS DBName,

  CURRENT_USER AS CurrentUser,

  HOST_NAME() AS HostName,

  APP_NAME() AS ApplicationName,

  OBJECT_NAME(@@PROCID) AS ProcedureName,

  USER_ID() AS Userid,

  USER_NAME() AS UserName,

  SUSER_ID() AS sUserid,

  SUSER_SNAME() AS sUserName,

  IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

  IS_MEMBER('db_owner') AS [Is_DB_owner],

  IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

  IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

  ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

  ConnectionProperty('net_transport') AS 'net_transport',

  ConnectionProperty('protocol_type') AS 'protocol_type',

  ConnectionProperty('auth_scheme') AS 'auth_scheme',

  ConnectionProperty('local_net_address') AS 'local_net_address',

  ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

  ConnectionProperty('client_net_address') AS 'client_net_address',

  ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

  Wow!

  ---