Sql server Database instance with SQL Authentication

RIAS
RIAS used Ask the Experts™
on
Hello,
I am trying to create a testing. Database1 instance of a database with SQL Authentication which was previously set on Windows Authentication and  dbo.
I have a Audit trial set on the StoredProcedures .
 The Audit Trial table (Table_Debug) has credential susername.

Query used in the Stored Procedure is
Insert into Table_Debug(sql)
After execution of the above query the entry in the table is like:

Table_Debug

SQL                                                Username  
"Select * from table1"                 "Network\Username"

Now after setting as the new database instance as  testing. Database1

The above query gives result as

SQL                                                Username  
"Select * from table1"                 "testing.Database1"


Any suggestion on how to get the user name who is connected to the Database.
Any change I can do on the credentials of the table.

Cheers
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
Commented:
I suspect you're currently using either USER_NAME or CURRENT_USER to obtain the user name. The issue here is that it's entirely possible to have a login that has sysadmin permissions and doesn't actually have a mapped user to the database in question. You probably want to use SYSTEM_USER instead. That will return the LOGIN if the user name is not available.
https://docs.microsoft.com/en-us/sql/t-sql/functions/system-user-transact-sql?view=sql-server-2017

Author

Commented:
Perfect!!!Exactly what I was looking for ....thanks a ton mate!!
Russ SuterSenior Software Developer

Commented:
Glad I could help. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial