Link to home
Start Free TrialLog in
Avatar of hanhn
hanhnFlag for Singapore

asked on

Equivalent statement for the Oracle, sys_context('USERENV','OS_USER'), in MSSQL

To the point,

Can anyone help me to convert the following oracle statement to MSSQL?

Thanks a millions !
SELECT sys_context('USERENV','OS_USER')  FROM dual

Open in new window

Avatar of Rob Farley
Rob Farley
Flag of Australia image

Try:

select suser_sname();

Rob

Avatar of hanhn

ASKER

Hi Rob,

Thanks for your fast response.

In Oracle, sys_context('USERENV','OS_USER') returns the Windows logon name of the user.

In MSSQL, suser_sname() returns the user login to the SQL instead of Windows.

Ya, so suser_sname() is not what i want.
ASKER CERTIFIED SOLUTION
Avatar of Rob Farley
Rob Farley
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hanhn

ASKER

Hmmm.. so can i conclude that MSSQL doesn't provide a function to capture the "real" user (Windows Logon) in the SQL connection session as Oracle does?
If you're using SQL Authentication, then the Windows Logon is basically ignored. It's best to use Integrated Security, so that the Windows Logon is the actual security mechanism used.

Rob
Avatar of hanhn

ASKER

I see.

The reason why i need to do so is because all users are accessing the same report using the same SQL connection (so that we won't have to maintain so many Windows Logon accounts in SQL Intergrated Security), in order to capture the different users using the report to update, in oracle we can do so by calling sys_context('USERENV','OS_USER'). Assuming user will only use their terminals to update the report.

Hmmm.. if that's the case, i will check with my boss how to go about it.

No other alternative?
Avatar of dportas
dportas

The alternative is to use Windows Integrated Security. You don't need to maintain lots of Windows login accounts in SQL Server. Just create a single domain user group and use that in SQL Server instead of your SQL login.
As David says, just assign an Active Directory group to have login permission, and life will become much easier to manage.

Rob
Avatar of hanhn

ASKER

Hmmm.. the SQL statement will be used in the trigger for audit purpose,

for example,
- the record in TABLE_X is created or updated by "USER_A".
- "USER_B" comes in to update or delete the record
- activate the trigger which does work INSTEAD OF UPDATE, DELETE
- The trigger will get the full @OLD.record and insert into TABLE_AUDIT appending a new column to capture the user who update or delete the record,  which is "USER_B" in this case.

Below i show the working oracle trigger and the MSSQL trigger:
-- In Oracle
 
CREATE OR REPLACE TRIGGER TRG_DETAIL_ROW BEFORE UPDATE OR DELETE ONTABLE_X
FOR EACH ROW 
DECLARE
strOSUser       VARCHAR2(50);
strAction       CHAR(1);
BEGIN
  IF UPDATING THEN
    strAction := 'U';
  ELSE
    strAction := 'D';
  END IF;
  SELECT sys_context('USERENV','OS_USER') INTO strOSUser FROM dual;
  INSERT INTO TABLE_AUDIT (col_a, col_b, col_c, aud_username, aud_action, aud_timestamp)
    VALUES (:OLD.col_a, :OLD.col_b, :OLD.col_c, strOSUser, strAction, sysdate);
END;
 
-- End of oracle sample
 
 
-- In MSSQL
 
CREATE TRIGGER TRG_DETAIL_ROW 
	ON TABLE_X INSTEAD OF UPDATE, DELETE 
AS
BEGIN
 
	
 
	DECLARE FOR_EACH_ROW_CURSOR CURSOR LOCAL FOR 
	SELECT *
	FROM  inserted 
	
	OPEN FOR_EACH_ROW_CURSOR 
	 WHILE (@@FETCH_STATUS <> -1)
	BEGIN
 
	DECLARE @strOSUser       VARCHAR(50) 
	DECLARE @strAction       CHAR(1) 
	IF (SELECT COUNT(*) FROM inserted) > 0 AND (SELECT COUNT(*) FROM deleted) > 0 
	BEGIN 
		SELECT @strAction  = '''U''' 
	END
	ELSE
	BEGIN 
		SELECT @strAction  = '''D''' 
	END
   
	SELECT @strOSUser  =  ????? -- this is where MSSQL doesn't provide the function
	
	INSERT INTO  TABLE_AUDIT   
			( col_a , 
			col_b , 
			col_c , 
			aud_username , 
			aud_action , 
			aud_timestamp )  
	 VALUES 		( @OLD.col_a , 
			@OLD.col_b , 
			@OLD.col_c , 
			@strOSUser , 
			@strAction , 
			GETDATE() )  
	
	IF (SELECT COUNT(*) FROM inserted) > 0 AND (SELECT COUNT(*) FROM deleted) > 0
	
		UPDATE TABLE_X
		 SET 
	
	IF (SELECT COUNT(*) FROM deleted) > 0 AND (SELECT COUNT(*) FROM inserted) = 0
		DELETE FROM TABLE_X WHERE EXISTS (SELECT * FROM deleted )
		FETCH FOR_EACH_ROW_CURSOR INTO 
	END
 
	CLOSE FOR_EACH_ROW_CURSOR
	DEALLOCATE FOR_EACH_ROW_CURSOR
END
 
 
GO
 
-- End of MSSQL sample

Open in new window

Yup, use Windows Auth and suser_sname() in your trigger, referring to a table called 'deleted' for the old records and 'inserted' for the new.

Rob
I don't think you've tested your trigger code in MSSQL. There are no row-level triggers in T-SQL. But you don't need them. Use the INSERTED and DELETED tables instead.

If you are using Enterprise Edition then it's better to use the Change Data Capture feature for audit purposes. No need for a trigger at all. Audit is built-in:
http://msdn.microsoft.com/en-us/library/bb522489.aspx