hanhn
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 !
Can anyone help me to convert the following oracle statement to MSSQL?
Thanks a millions !
SELECT sys_context('USERENV','OS_USER') FROM dual
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.
Thanks for your fast response.
In Oracle, sys_context('USERENV','OS_
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Rob
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?
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_
Hmmm.. if that's the case, i will check with my boss how to go about it.
No other alternative?
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
Rob
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:
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
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
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
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
select suser_sname();
Rob