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

hanhnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rob_farleyCommented:
Try:

select suser_sname();

Rob

0
hanhnAuthor Commented:
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.
0
rob_farleyCommented:
Ah... if you use Windows Authentication to connect to SQL, it returns the Windows logon name.

If you're not connecting using Windows Authentication, I don't think that information is available.

Happy to be wrong though...

Rob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hanhnAuthor Commented:
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?
0
rob_farleyCommented:
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
0
hanhnAuthor Commented:
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?
0
dportasCommented:
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.
0
rob_farleyCommented:
As David says, just assign an Active Directory group to have login permission, and life will become much easier to manage.

Rob
0
hanhnAuthor Commented:
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

0
rob_farleyCommented:
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
0
dportasCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.